Re: sql question

> Mark.Denham@bbc.co.uk (Mark Denham) writes: > In article <DGAt5o.F98@ireq.hydro.qc.ca>, > delmas@ireq.hydro.qc.ca (Herve Delmas) wrote: > > How can we made a sql request on a huge table that will get out > >the last lines without using the rowid column? > > > >ex.: let's say that count(*) return 10000 on this table. If we want to get > > > the last fifty lines, for now we are doing: > > > > select col1, col2 from table > > where rowid > 9950> > > > Any help will be appreciate! > Another thought on the subject that you might consider is to use a scroll > cursor for the select. > > All you need to do then is OPEN the cursor, FETCH LAST to get last row, > followed by 49 FETCH PREVIOUS calls..... As some have mentioned this will give a very large temporary table to store the cursor data. I haven't seen any responses however suggesting the use of a descending index on a serial field. If you include a serial field, define a descending index on it and do an order by on this field (without any where clause at all), you could use a simple foreach loop to fetch the 50 last inserted records without any problems with a growing temporary table. Also be aware that your original solution probably won't work if you ever delete a record from your table. Also, I am not quite sure that rowid's are simple numbers as you assume, particularly not on OnLine ver. 6.0 and later. Rowid was always a bad idea introduced to solve problems that should have been solved by other means. Nils.Myklebust@ccmail.telemax.no NM-data, Dalsbergstien 7, N-0170 Oslo, Norway My opinions are those of my company