Re: sql question

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 to the 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 #serialno. 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