Re: [sqlite] can you select series with SQL?
Thanks; that to me looks a truly amazing SQL! As you say doing this in code might be more efficient and definitely less confusing. In fact when doing something as you suggest: select * from MyTable where ID = 14 order by ID desc I can make it a lot more efficient by adding a limit as not many records will be needed. RBS On Sun, Nov 20, 2011 at 12:15 AM, Igor Tandetnik itandet...@mvps.org wrote: Bart Smissaert bart.smissa...@gmail.com wrote: If we have the 14 (we know to start at 14) can we select the records 14, 13, 12 and 11, so the consecutive numbers, going down from 14? select * from MyTable t1 where (select count(*) from MyTable t2 where t2.ID between t1.ID and 14) == 14 - t1.ID + 1; Personally, I'd just run a query like select * from MyTable where ID = 14 order by ID desc; and step through it until the next ID is non-consecutive. -- Igor Tandetnik ___ 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] can you select series with SQL?
Bart Smissaert bart.smissa...@gmail.com wrote: In fact when doing something as you suggest: select * from MyTable where ID = 14 order by ID desc I can make it a lot more efficient by adding a limit as not many records will be needed. There's no difference between adding a LIMIT N clause to the query, and simply calling sqlite3_step N times and then resetting or finalizing. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can you select series with SQL?
I am not not working that close to the SQLite source to talk about sqlite3_step etc. as I am using a VB wrapper. Still, I suppose what you say still applies. As it turns out and can now beforehand (without checking for non-consecutive id numbers) how many records should be fetched, so with that things are in fact simple. RBS On Sun, Nov 20, 2011 at 2:12 PM, Igor Tandetnik itandet...@mvps.org wrote: Bart Smissaert bart.smissa...@gmail.com wrote: In fact when doing something as you suggest: select * from MyTable where ID = 14 order by ID desc I can make it a lot more efficient by adding a limit as not many records will be needed. There's no difference between adding a LIMIT N clause to the query, and simply calling sqlite3_step N times and then resetting or finalizing. -- Igor Tandetnik ___ 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] can you select series with SQL?
Say we have a table table1 with unique integer field ID. Now we have the following data: ID 1 2 3 4 11 12 13 14 If we have the 14 (we know to start at 14) can we select the records 14, 13, 12 and 11, so the consecutive numbers, going down from 14? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can you select series with SQL?
ID 1 2 3 4 11 12 13 14 If we have the 14 (we know to start at 14) can we select the records 14, 13, 12 and 11, so the consecutive numbers, going down from 14? Sure: select id from yourtable where id = 14 limit 4 order by id desc; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can you select series with SQL?
Bart Smissaert bart.smissa...@gmail.com wrote: If we have the 14 (we know to start at 14) can we select the records 14, 13, 12 and 11, so the consecutive numbers, going down from 14? select * from MyTable t1 where (select count(*) from MyTable t2 where t2.ID between t1.ID and 14) == 14 - t1.ID + 1; Personally, I'd just run a query like select * from MyTable where ID = 14 order by ID desc; and step through it until the next ID is non-consecutive. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can you select series with SQL?
so the consecutive numbers, going down from 14? Sorry I interpreted the question in a dumb way. Igor is certainly right (as usual). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
