Re: sql question

An enhancement to the suggestion made would be to use a SCROLL cursor and then fetch absolute COUNT-50. Then you could fetch next the rest of the records > 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! > > > >======================================================================= > ======>--- Herve Delmas ---- delmas@ireq.hydro.qc.ca > >======================================================================= > ======> > > > > 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! > > > I assume that you're only interested in the most recent 50 rows inserted > tothe table, in that case, you can't rely on the value of rowid's to get > the newest 50 rows. > Add a column of type SERIAL to your table. > For a relatively small table Try this; > let i = 1 > declare c_fifty cursor for > select serial_col, col1, col2 > from tablename > where clause goes here if needed! > order by serial_col desc > ^^^^> foreach c_fifty into gr_fifty[i].* > display gr_fifty[i] > if i > 49 then > exit foreach > end if > let i = i + 1 > end foreach > > In case of a real huge table, sorting desc is so expensive. > You may want work with this piece of 4gl code. > ######################################################################## > ###### database tstdb > main > call tab_tail() > end main > ######################################################################## > ###### function tab_tail() > define i integer > define max_serial_no integer > define gr_fifty array[50] of record > serial_col integer, > val1 char(8), > val2 char(8) > end record > prepare c_fifty from " select serial_col, val1, val2 from bigtab > where serial_col < ? " > insert into bigtab values (0, "val1", "val2") > if sqlca.sqlcode = 0 then > let max_serial_no = sqlca.sqlerrd[2] > delete from bigtab where serial_col = max_serial_no> #serial no. wasted. > else > error "ERROR: ", sqlca.sqlcode > return > end if > open c_fifty using max_serial_no > if sqlca.sqlcode != 0 then > error "ERROR: ", sqlca.sqlcode > return > end if > let i = 1 > foreach c_fifty into gr_fifty[i].* > display gr_fifty[i].* > if i > 49 then > exit foreach > end if > let i = i + 1 > end foreach > end function > ######################################################################## > ###### > > -- > ahmed.khafagy@washingtondc.attgis.com Malcolm Weallans Online Database Consultancy Phone 0628-72154 Fax 0628-37463 CIX - onlinedbc