SQL Command to obtain from RowID to EOF?

Hello all,

I want to retrieve only new data from a SQLite file. I keep track of the last RowID obtained, and want to start from the next one (RowID +) to the End of the file. I know I can do this with a brute force method, get all, the loop through. But that just seems like a huge waste and very ‘ineligant’ way to do it. I could also figure out how to get the last record then do a command from bRowID to eRowID. But here again, I thought maybe one of the expert SQL people in the forum may know how to do this in more proper and elegant way.

Ideas and sample code would be really appreciated!
Thank you,
Tim

SELECT *
FROM my_table
WHERE rowid > [insert the last rowid here]
ORDER BY rowid

Thanks Kem!
Will try that, I appreciate the rapid response!

Tim

Be careful of using the actual RowID for this. It’s much better to have an AutoIncrement Primary Key. RowID is not guaranteed to remain constant. It can change under certain circumstances.

Hi Tim,

Thanks for your input. I have a column called rowid which is an autoincrement column,

Tim

you should not name a column with ‘rowid’ … unless you’re looking for trouble !

you could mark processed rows as ‘handled’ as you complete them.

Then select all rows that are not flagged as processed.

I know some will disagree with this, but we feel that the primary key should have a meaningful name. Example, if your table name is Artist then the primary key, auto-increment, column name should be Artist_ID (the underscore is up to you). And any foreign key references to Artist (via Artist_ID) are then perfectly clear.

This does several things: 1) Tells me the table and that it’s a primary key. 2) And if you have many tables that you’re going to join together in SQL statements you don’t have to do any aliasing because they’re already unique names.

The database design should make things very explicit. It has the further advantage of making your code more explicit. Nothing worse than trying to debug someone’s code when all primary keys are named ID. Sometimes it’s no big deal but the more complex the project the bigger deal this becomes.

anytime you do maintenance you have a good chance to change the RowID for the table. Depending on the Database Engine, will chose the likelihood of the change.