On Wed, Apr 24, 2013 at 5:09 PM, Gary Baranzini <[hidden email]> wrote:
> I have the following query:
> SELECT id FROM pointslocation WHERE id IN (1,7,3,4,5,2,6)
> What 's returned is 1,2,3,4,5,6,7.
The output order for rows in a SELECT statement is undefined in SQL if you
do not use an ORDER BY clause. The current SQLite implementation gives the
order you show above, but this might change from one release to the next,
so you should not depend on it.
> How do I retain the order in the IN list?
You would need to provide an ORDER BY clause that somehow coerces the
output into the order you desire. That will be tricky in this case.
Probably you will need an auxiliary table. Perhaps something like the
following: (Warning - untested code)
CREATE TABLE sortorder(x,y);
INSERT INTO sortorder VALUES(1,1),(7,2),(3,3),(4,4),(5,5),(2,6),(6,7);
SELECT id FROM pointslocation JOIN sortorder ON id=x
WHERE id IN (1,7,2,3,5,2,6)
ORDER BY y;