Select WHERE IN List ordering

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Select WHERE IN List ordering

Gary Baranzini
Hi,

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.

How do I retain the order in the IN list?

jb
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Select WHERE IN List ordering

Simon Slavin-3

On 24 Apr 2013, at 10:09pm, 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.
>
> How do I retain the order in the IN list?

There's no simple way to do that in SQL.  The numbers in the brackets is a set: an unordered collection.  A number is either in the list or not, the list has no inherent order.

If I wanted to select all rows from pointslocation in a particular order I'd probably make another column in pointslocation with the ordinals in.

If I wanted to select just a few records in that order I'd make another TABLE (possibly using CREATE TEMPORARY TABLE) which had the ordinals in ...

pl_id sel_order
1 1
7 2
3 3
4 4
5 5
2 6
6 7

Then I'd use a SELECT JOIN to pull up the pointslocation rows in that order.  Possibly something like

SELECT pointslocation.* FROM myTable JOIN pointslocation ON pointslocation.id=myTable.pl_id ORDER BY myTable.sel_order

but I can't test the above here so don't assume it's definitely going to work.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Select WHERE IN List ordering

Richard Hipp-3
In reply to this post by Gary Baranzini
On Wed, Apr 24, 2013 at 5:09 PM, Gary Baranzini <[hidden email]> wrote:

> Hi,
>
> 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;





> jb
> ______________________________**_________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Select WHERE IN List ordering

Clemens Ladisch
In reply to this post by Gary Baranzini
Gary Baranzini wrote:
> SELECT id  FROM pointslocation  WHERE id IN (1,7,3,4,5,2,6)
>
> How do I retain the order in the IN list?

If you don't want to create a (temporary) table for the ordering, you
can also do the mapping from id to the order in the query itself:

  SELECT id
  FROM pointslocation
  WHERE id IN (1,7,3,4,5,2,6)
  ORDER BY CASE id
           WHEN 1 THEN 1
           WHEN 7 THEN 2
           WHEN 3 THEN 3
           WHEN 4 THEN 4
           WHEN 5 THEN 5
           WHEN 2 THEN 6
           WHEN 6 THEN 7
           END




Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Select WHERE IN List ordering

Simon Slavin-3

On 25 Apr 2013, at 1:58pm, Clemens Ladisch <[hidden email]> wrote:

>  SELECT id
>  FROM pointslocation
>  WHERE id IN (1,7,3,4,5,2,6)
>  ORDER BY CASE id
>           WHEN 1 THEN 1
>           WHEN 7 THEN 2
>           WHEN 3 THEN 3
>           WHEN 4 THEN 4
>           WHEN 5 THEN 5
>           WHEN 2 THEN 6
>           WHEN 6 THEN 7
>           END

Clever.  Could generate that clause in code.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Select WHERE IN List ordering

joshuapinter
In reply to this post by Clemens Ladisch
You saved my bacon with this one. Just wanted to pop in and say a quick
thanks to you. :)



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users