Re: [sqlite] Nth row of on sqlite DB
Hei, Try the following sql statements: To get the first n rows : SELECT * FROM table-name LIMIT n OFFSET 0 To get the nth entry : SELECT * FROM table-name LIMIT 1 OFFSET n-1 Suresh Kavita Raghunathan 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 ? Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://old.nabble.com/Nth-row-of-on-sqlite-DB-tp29963848p31002137.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Nth row of on sqlite DB
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 ? Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nth row of on sqlite DB
3. How can I make my primary ID remain sequential even after a delete of row. Can sqlite somehow realign the indices after a row in the middle is deleted ? On 10/14/10 10:53 AM, Kavita Raghunathan kavita.raghunat...@skyfiber.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 ? Thanks, Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nth row of on sqlite DB
On Thu, Oct 14, 2010 at 10:54 AM, Kavita Raghunathan kavita.raghunat...@skyfiber.com wrote: 3. How can I make my primary ID remain sequential even after a delete of row. Can sqlite somehow realign the indices after a row in the middle is deleted ? If you can change the primary ID (sic), by which, I am assuming you are referring to the primary key, then it won't really be the primary key. PK should be immutable. Create a separate ID that can be under your control, and change it as you wish. On 10/14/10 10:53 AM, Kavita Raghunathan kavita.raghunat...@skyfiber.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. Thanks, Kavita ___ 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
Re: [sqlite] Nth row of on sqlite DB
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. 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 :) 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
Re: [sqlite] Nth row of on sqlite DB
I found an example: Select * from tblxyz order by ID where limit 1 offset n-1 Will retreive the nth row. In this case order by ID is probably not necessary as its already ordered with a few missing rows. Thank you! Kavita On 10/14/10 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. 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 :) 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nth row of on sqlite DB
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
Re: [sqlite] Nth row of on sqlite DB
On 14/10/10 17:26, Kavita Raghunathan wrote: I found an example: Select * fromtblxyz order by ID where limit 1 offset n-1 Will retreive the nth row. In this case order by ID is probably not necessary as its already ordered with a few missing rows. It might happen to be ordered - but that is an implementation detail. Unless you use the ORDER BY clause the database may deliver the records in any order it wishes. It is not forced to use the ID order. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nth row of on sqlite DB
I see. I was not aware of this because the display results of select * from TBLXYZ has always shown up in ascending order of ID. I don't recall a single instance where it was delivered out of order, and hence my assumption. Now I understand Kishore's comment better about the DB not knowing about occurance and having to use order by. Will do. Kavita On 10/14/10 12:10 PM, Alan Chandler a...@chandlerfamily.org.uk wrote: On 14/10/10 17:26, Kavita Raghunathan wrote: I found an example: Select * fromtblxyz order by ID where limit 1 offset n-1 Will retreive the nth row. In this case order by ID is probably not necessary as its already ordered with a few missing rows. It might happen to be ordered - but that is an implementation detail. Unless you use the ORDER BY clause the database may deliver the records in any order it wishes. It is not forced to use the ID order. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
