On Mon, 30 Jan 2006 05:55:34 -0800, runescience wrote:

> Hi Folks. I have a large number of records that are deliverd for web view.
> This may sound a bit wierd but its a user requirement.
>
> I will have 100,000 records for view to the user. Currently I send records
> to a temp tableand view them in a web page in java. There I use the "<<
> < 1 2 3 4 5... 99 100 > >>" method at the bottom of the page to allow
> the user to view the chunks.
>
> select * from tablename where rowid > first chunk and rowid < next chunk.
>
> Now the user wishes to do a sort on up to 3 columns. This ruins my old
> method of doing things. I cant scroll out chunks, because a select (
> Select * from table name order by col8, col6, col3 ) does not reorder the
> rowid.
>
> So, then I thought of doing a 'Select into' a new temp table. But, then I
> have to keep track of the new temp table name each time. I read some
> where, and I hope its correct, that when you create a temp table any new
> records are stored in order of the select into.

I would use a secondary table that only has the original row id (or
identity value) for the row, and a second column that has the row id for
the sorted data. Creating this second table should be pretty fast, and you
can then do the retrieves based on the two tables (a simple join with an
order by on the second table's row id).

You can also simplify your code by always using the two table method. For
the original sort you simply have the secondary table with identical
column values.

Michael
--
Michael Peppler [TeamSybase] mpeppler@peppler.org - http://www.peppler.org/
Sybase DBA/Developer
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html