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
