Re: sql question

> > > 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! > > My only suggestion is to unload the table including the rowid > and make a copy of the current table definition before dropping it. > > Before reloading, add a serial column to the table. This serial column > is going to be used to store the unloaded rowids. > > Reload the table and use the serial key. > > This will of course rely on the fact that you do not delete from the table > and you will [possibly] need to change all your inserts! > Mark is correct when he says that you wouldn't be able to perform deletes on the table. In addition, though, you also wouldn't be able to perform rollbacks! Prove this to yourself by trying the following statements: CREATE TABLE test(id_col serial); BEGIN WORK; INSERT INTO test VALUES (0); ROLLBACK WORK; BEGIN WORK; INSERT INTO test VALUES (0); COMMIT WORK; You would probably suspect that the table would have one row with a serial value of 1. But, you'd be wrong. Value 1 was taken by the first insert EVEN THOUGH IT WAS ROLLED BACK! Instead, you end up with value 2. So, so much for using serial values. Rowids also have problems in that they're not guarenteed to reflect the insert order of the rows (especially in the newer engine versions). Probably the best alternative is to add a time stamp or a manually tracked "row number" column. Both are a pain in the neck, but they are the best way for things like "find the last 50 rows I inserted." --Craig (craig@chemistry.utah.edu)