On 10/18/08, Andrew Gatt <[EMAIL PROTECTED]> wrote: > Jonas Sandman wrote: > > Just to point out the obvious, have you tried ORDER BY? > > > > "SELECT name FROM table ORDER BY name;" will return your list in > > alphabetical order. > > > > /Jonas > > > > > > Thanks for the suggestion, but it needs to be an order i can specify, > not just ordered. I.e. i may want row 45 first, then 32 then 67 etc... >
Create a column to control your specified order and populate it accordingly. Then SELECT ... FROM ... WHERE .. IN () ... ORDER BY col_custom_order > > > On Sat, Oct 18, 2008 at 6:53 PM, Andrew Gatt <[EMAIL PROTECTED]> wrote: > > > >> Andrew Gatt wrote: > >> > >>> I'm not sure if i'm missing something, but is there an efficient way of > >>> retrieving multiple rows based on different conditions in order. For > >>> example i have a table with rows of ids, i want to select multiple rows > >>> at a time. At present i am doing a "SELECT name FROM table WHERE id = x" > >>> for each row i want and then stitching it all together. But i'm finding > >>> this is quite slow even on a moderately small database (2000 entries). > >>> > >>> I'm guessing my SQL is the worst way of doing things so i've been trying > >>> to find a better method. I stumbled across "SELECT name FROM table WHERE > >>> id IN (x,y,z) however this doesn't allow me to specify the order the > >>> rows are returned, which i must have. > >>> > >>> The only other option i can find is using UNION ALL in between multiple > >>> SELECT statements, but would this give me a large performance increase > >>> over doing this progammatically as i've got it? > >>> > >>> Unless i've missed something obvious which could well be the case! > >>> > >>> > >>> > >>> > >> After trying several methods to improve the SQL the only thing that > >> really made a difference was creating an index on the ids. Using a UNION > >> ALL did improve matters, but you end up have to concatenate a very long > >> string for the query, so if anyone does have any SQL ideas i'd like to > >> hear them. > >> > >> Andrew > >> > >> > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.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
