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
