Re: [sqlite] Select Limit issues
"Webmaster" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Ok, Did that but now it doesn't run? > > select * from ( >select * from table1 where Field2='Red' order by Field1 limit 1 > offset :X) I meant that you would put an actual number in place of :X. Or else, that you would figure out how to work with parameterized queries in whatever environment you use SQLite with. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select Limit issues
This kind of problem, in my opinion, is more suited for the application rather than SQL. Even if you get this to work, there are several issues... what happens if your dataset changes? what happens if there are not enough Whites or Blues before or after the first Red? What happens if a White or a Blue gets intermingled/interchanged with each other? How do you maintain the order of the list that you get back? In your original data, Field1 is definitely not the ordering column... see the following rows 1218.90,White,12:00 18516.40,Blue,12:00 1255.33,White,12:00 927.46,White,12:00 etc. etc. That said, see http://www.perlmonks.org/?node_id=726949 for a couple of three solutions more suitably belonging in the application domain rather than the SQL domain. On 11/30/08, Webmaster <[EMAIL PROTECTED]> wrote: > Ok, Did that but now it doesn't run? > > select * from ( > select * from table1 where Field2='Red' order by Field1 limit 1 > offset :X) > > union all > select * from ( > select * from table1 where Field2='White' and > > Field1 < (select Field1 from table1 where Field2='Red' order by > Field1 limit 1) > > order by Field1 desc limit 4 > ) > union all > select * from ( > select * from table1 where Field2='Blue' and > > Field1 < (select Field1 from table1 where Field2='Red' order by > Field1 limit 1) > > order by Field1 desc limit 1 > ) > union all > select * from ( > select * from table1 where Field2='White' and > > Field1 > (select Field1 from table1 where Field2='Red' order by > Field1 limit 1) > > order by Field1 limit 4 > ) > union all > select * from ( > select * from table1 where Field2='Blue' and > > Field1 > (select Field1 from table1 where Field2='Red' order by > > Field1 limit 1) > order by Field1 limit 1 > ) > > order by Field1; > > > > I take it I am missing something here? > > > > > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik > > Sent: Sunday, November 30, 2008 9:15 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Select Limit issues > > > "Webmaster" <[EMAIL PROTECTED]> wrote > in message news:[EMAIL PROTECTED] > > Looping. > > > > Is there a way to put this into a loop where it starts again but with > > the > > next Field2 ='Red' > > Replace all subqueries that look like this: > > (select Field1 from table1 where Field2='Red' > order by Field1 limit 1) > > with something like this: > > (select Field1 from table1 where Field2='Red' > order by Field1 limit 1 offset :X) > > and vary X in the loop, starting from 0, until you get an empty > resultset. > > 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 > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select Limit issues
Ok, Did that but now it doesn't run? select * from ( select * from table1 where Field2='Red' order by Field1 limit 1 offset :X) union all select * from ( select * from table1 where Field2='White' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 4 ) union all select * from ( select * from table1 where Field2='Blue' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 1 ) union all select * from ( select * from table1 where Field2='White' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 4 ) union all select * from ( select * from table1 where Field2='Blue' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 1 ) order by Field1; I take it I am missing something here? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Sunday, November 30, 2008 9:15 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Select Limit issues "Webmaster" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Looping. > > Is there a way to put this into a loop where it starts again but with > the > next Field2 ='Red' Replace all subqueries that look like this: (select Field1 from table1 where Field2='Red' order by Field1 limit 1) with something like this: (select Field1 from table1 where Field2='Red' order by Field1 limit 1 offset :X) and vary X in the loop, starting from 0, until you get an empty resultset. 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] Select Limit issues
"Webmaster" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Looping. > > Is there a way to put this into a loop where it starts again but with > the > next Field2 ='Red' Replace all subqueries that look like this: (select Field1 from table1 where Field2='Red' order by Field1 limit 1) with something like this: (select Field1 from table1 where Field2='Red' order by Field1 limit 1 offset :X) and vary X in the loop, starting from 0, until you get an empty resultset. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select Limit issues
Looping. Is there a way to put this into a loop where it starts again but with the next Field2 ='Red' -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Sunday, November 30, 2008 2:20 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Select Limit issues "Webmaster" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Now I am getting the following error: > SQL Error: ORDER BY clause should come after UNION ALL not before Right, you need another level of indirection: select * from ( select * from table1 where Field2='Red' order by Field1 limit 1 ) union all select * from ( select * from table1 where Field2='White' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 4 ) union all select * from ( select * from table1 where Field2='Blue' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 1 ) union all select * from ( select * from table1 where Field2='White' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 4 ) union all select * from ( select * from table1 where Field2='Blue' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 1 ) order by Field1; Personally, I'd implement logic like this in my application code, rather than in ever-more-complex SQL statements. 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] Union, limit, order query issues
Anybody that is way better than me please help. For the life of me I can not get this to work. select * from ( select * from table1 where Field2='Red' order by Field1 limit 1 union all select * from table1 where Field2='White' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 4 union all select * from table1 where Field2='Blue' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 1 union all select * from table1 where Field2='White' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 4 union all select * from table1 where Field2='Blue' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 1 ) order by Field1; -- View this message in context: http://www.nabble.com/Union%2C-limit%2C-order-query-issues-tp20761996p20761996.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
Re: [sqlite] Select Limit issues
Work. But one small issue. The results are not in order. Once I changes field1 to "Real" the order by field1 worked great! "implement logic like this in my application code, rather.." That's an idea. To bad I do not know Delphi. I have a program that is built by Delphi called "PrettyReports" I do know that I can put Delphi code into the "reports" but I would not have a clue. Thanks a bunch. Now I need to grow my hair back :-) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Sunday, November 30, 2008 2:20 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Select Limit issues "Webmaster" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Now I am getting the following error: > SQL Error: ORDER BY clause should come after UNION ALL not before Right, you need another level of indirection: select * from ( select * from table1 where Field2='Red' order by Field1 limit 1 ) union all select * from ( select * from table1 where Field2='White' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 4 ) union all select * from ( select * from table1 where Field2='Blue' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 1 ) union all select * from ( select * from table1 where Field2='White' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 4 ) union all select * from ( select * from table1 where Field2='Blue' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 1 ) order by Field1; Personally, I'd implement logic like this in my application code, rather than in ever-more-complex SQL statements. 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] Select Limit issues
Hello! В сообщении от Sunday 30 November 2008 23:19:52 Igor Tandetnik написал(а): > Personally, I'd implement logic like this in my application code, rather > than in ever-more-complex SQL statements. May be as create temp table A; insert into A select * from table1 where Field2='Red' order by Field1 limit 1; insert into A select * from table1 where Field2='White' and Field1 < (select Field1 from A where Field2='Red') order by Field1 desc limit 4; insert into A select * from table1 where Field2='Blue' and Field1 < (select Field1 from A where Field2='Red') order by Field1 desc limit 1; insert into A select * from table1 where Field2='White' and Field1 > (select Field1 from A where Field2='Red') order by Field1 limit 4; select * from A order by Field1; Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select Limit issues
"Webmaster" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Now I am getting the following error: > SQL Error: ORDER BY clause should come after UNION ALL not before Right, you need another level of indirection: select * from ( select * from table1 where Field2='Red' order by Field1 limit 1 ) union all select * from ( select * from table1 where Field2='White' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 4 ) union all select * from ( select * from table1 where Field2='Blue' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 1 ) union all select * from ( select * from table1 where Field2='White' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 4 ) union all select * from ( select * from table1 where Field2='Blue' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 1 ) order by Field1; Personally, I'd implement logic like this in my application code, rather than in ever-more-complex SQL statements. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select Limit issues
Now I am getting the following error: SQL Error: ORDER BY clause should come after UNION ALL not before Any ideas? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Sunday, November 30, 2008 9:41 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Select Limit issues "Webmaster" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I am trying query for this. > Find a first occurrence of "Red" > Then find searching from this record find the previous 4 records that > contain "White" Then find the Previous 1 that contains "Blue" > Now do the same thing but searching "next" select * from ( select * from table1 where Field2='Red' order by Field1 limit 1 union all select * from table1 where Field2='White' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 4 union all select * from table1 where Field2='Blue' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 1 union all select * from table1 where Field2='White' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 4 union all select * from table1 where Field2='Blue' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 1 ) order by Field1; 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] Select Limit issues
"Webmaster" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I am trying query for this. > Find a first occurrence of "Red" > Then find searching from this record find the previous 4 records that > contain "White" Then find the Previous 1 that contains "Blue" > Now do the same thing but searching "next" select * from ( select * from table1 where Field2='Red' order by Field1 limit 1 union all select * from table1 where Field2='White' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 4 union all select * from table1 where Field2='Blue' and Field1 < (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 desc limit 1 union all select * from table1 where Field2='White' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 4 union all select * from table1 where Field2='Blue' and Field1 > (select Field1 from table1 where Field2='Red' order by Field1 limit 1) order by Field1 limit 1 ) order by Field1; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select Limit issues
Here is my database as a csv. Field1,Field2,Field3 1.00,Blue,12:00 40.20,White,12:00 80.30,White,12:00 120.00,White,12:00 126.00,White,12:00 162.43,White,12:00 198.86,White,12:00 235.29,White,12:00 271.72,Red,03:45 308.15,White,12:00 344.58,White,12:00 381.01,White,12:00 417.44,White,12:00 453.87,White,12:00 490.30,White,12:00 526.73,White,12:00 563.16,Red,07:45 599.59,White,12:00 636.02,White,12:00 672.45,White,12:00 708.88,White,12:00 745.31,White,12:00 781.74,White,12:00 818.17,White,12:00 854.60,Blue,12:00 891.03,White,12:00 963.89,White,12:00 1000.32,White,12:00 1036.75,Red,08:30 1073.18,White,12:00 1109.61,Red,06:00 1146.04,White,12:00 1182.47,White,12:00 1218.90,White,12:00 18516.40,Blue,12:00 1255.33,White,12:00 927.46,White,12:00 I am trying query for this. Find a first occurrence of "Red" Then find searching from this record find the previous 4 records that contain "White" Then find the Previous 1 that contains "Blue" Now do the same thing but searching "next" My results would look like this: 1.00,Blue,12:00 126.00,White,12:00 162.43,White,12:00 198.86,White,12:00 235.29,White,12:00 271.72,Red,03:45 308.15,White,12:00 344.58,White,12:00 381.01,White,12:00 417.44,White,12:00 854.60,Blue,12:00 I was thinking more like a "Rats Nest" :-) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor Sent: Saturday, November 29, 2008 7:24 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Select Limit issues On 11/29/08, Webmaster <[EMAIL PROTECTED]> wrote: > I have never posed in a group before forgive me if I am out of line with my > question. > > I have a table called table1 > With three fields: Field1, Field2, Field3 > > Here is my query but I get errors. > Any help work be great Please provide more details... the table schema, number of rows in the table, sample data, and most importantly, the actual errors you are getting. That said, the query below is, if you don't mind it, absolutely... what description should I use... a rat's maze. How about constructing views out of each component query, testing to see each view works, then building up from that till you actually encounter whatever errors you are getting? > > select field1, field2 from > ( select * from ee_table1 >where field2 = 'blue' limit 1 >and field1 < (select min(field1) from (select * from ee_table1 >where field2 = 'white' limit 4 >and field1 < (select field1 from > ee_table1 where field2 = 'red' limit 1 ) order by field1 desc) a ) >order by field1 desc > ) previous_blue > > union all > > select field1,field2 from > ( select * from ee_table1 >where field2 = 'white' limit 4 > and field1 < (select field1 from ee_table1 where field2 = 'red' limit > 1) >order by field1 desc > ) previous_5_white > > union all > > (select field1, field2 from ee_table1 where field2 = 'red' limit 1) > > union all > > select field1,field2 from > ( select * from ee_table1 >where field2 = 'white' limit 4 > and field1 > (select field1 from ee_table1 where field2 = 'red' limit > 1) >order by field1 asc > ) next_5_white > > union all > > select field1,field2 from > ( select * from ee_table1 >where field2 = 'blue' limit 1 >and field1 > (select max(field1) from (select * from ee_table1 >where field2 = 'white' limit 4 >and field1 > (select field1 from > ee_table1 where field2 = 'red' limit 1) order by field1 asc) a ) >order by field1 asc > ) previous_blue > > > > > Thanks in advance > Sticky -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ 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] how do this stuff in sqlite
Define "don't work". Do you get an error? What's the error text? i mean it can be run but the result doesn't like mysql do. whats wrong with my query? Begin and End are keywords in SQLite (SQL is case insensitive). If you insist on naming your columns this way, you have to enclose the names in double quotes, just as you did when creating awal1 and akhir1. thanks but, i think we don't need to quote begin or end coz this is some exceprtion. like mysql does. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
