On Thu, Oct 14, 2010 at 11:15 AM, Kavita Raghunathan <kavita.raghunat...@skyfiber.com> wrote: > Please see comment > > > On 10/14/10 11:02 AM, "P Kishor" <punk.k...@gmail.com> wrote: > > >>> Hello, >>> I¹ve been adding and deleting rows from the sqlite database. Now the primary >>> ID is non-sequential. >>> >>> >>> 1. How do I get the nth entry in the database >>> 2. AND, How do I get the first n rows from the database ? >>> >> >> Both "nth" and "first n" depend upon the sort order. Sort the table >> the way you want, and then get the nth or the first n (use >> LIMIT/OFFSET) to get the desired rows. >> >> The db by itself has no sort order, although, if you have numeric PK, >> there is an implicit ascending sort on PK. >> > > Yes, I have numeric PK. I don't want to sort the table, the way it was added > in ascending order of PK, I want it the same way, because the entries are > historical events based on order of occurance.
The database has no concept of "order of occurrence," you do. So, create a column in which you can store the timestamp for when the row was created. Then, use that to order the result and use LIMIT/OFFSET to restrict the rows you want returned. > Now, when I added, the PK was > 1,2,3,...100. I have since say deleted 2,5,11 because the events are no > longer relavant. Now I want the first 10 entries, which will be > 1,3,4,6,7,8,9,10,12,13. You are saying I can use LIMIT/OFFSET to do that? I > do not need to order. I'm looking for the simplest and most efficient way to > do this. I know, arent we all :) There is nothing complicated or inefficient about enforcing the sort order that you want. You do need an order, because without an order there is no sense to "first" or "first n." PK is generally controlled by the db (although, it doesn't have to be). PK definitely should be immutable, and non-reusable, unless you are enforcing FKs with cascade DELETEs because it might be a foreign key in another table. > > Kavita >>> >>> Thanks, >>> Kavita >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
