ORDER BY the IN value list

Question

I have a simple SQL query in PostgreSQL 8 3 that grabs a bunch of comments  I provide a sorted list of values to the IN construct in the WHERE clause   SELECT   FROM comments WHERE  comments id IN  1 3 2 4      This returns comments in an arbitrary order which in my happens to be ids like 1 2 3 4   I want the resulting rows sorted like the list in the IN construct   1 3 2 4   How to achieve that

User · Answer

SELECT   FROM  comments  JOIN     SELECT 1 as  id  1 as  order  UNION ALL    SELECT 3 2 UNION ALL SELECT 2 3 UNION ALL SELECT 4 4   j ON  comments   id    j  id  ORDER BY j ORDER   or if you prefer evil over good   SELECT   FROM  comments  WHERE   comments   id  IN  1 3 2 4   ORDER BY POSITION      comments   id      IN   1 3 2 4

User · Answer

With Postgres 9 4 this can be done a bit shorter   select c   from comments c join     select     from unnest array 43 47 42   with ordinality   as x  id  ordering  on c id   x id order by x ordering    Or a bit more compact without a derived table   select c   from comments c   join unnest array 43 47 42   with ordinality as x  id  ordering       on c id   x id order by x ordering   Removing the need to manually assign maintain a position to each value    With Postgres 9 6 this can be done using array position     with x  id list  as     values  array 42 48 43     select c   from comments c  x where id   any  x id list  order by array position x id list  c id     The CTE is used so that the list of values only needs to be specified once  If that is not important this can also be written as   select c   from comments c where id in  42 48 43  order by array position array 42 48 43   c id

User · Answer

On researching this some more I found this solution   SELECT   FROM  comments  WHERE   comments   id  IN  1 3 2 4    ORDER BY CASE  comments   id  WHEN 1 THEN 1 WHEN 3 THEN 2 WHEN 2 THEN 3 WHEN 4 THEN 4 END   However this seems rather verbose and might have performance issues with large datasets  Can anyone comment on these issues

User · Answer

select   from comments where comments id in   select unnest ids  from bbs where id 19795   order by array position  select ids from bbs where id 19795  comments id    here   bbs  is the main table that has a field called ids  and  ids is the array that store the comments id    passed in postgresql 9 6

User · Answer

I think this way is better    SELECT   FROM  comments  WHERE   comments   id  IN  1 3 2 4       ORDER BY  id 1 DESC  id 3 DESC  id 2 DESC  id 4 DESC

User · Answer

In Postgres 9 4 or later  this is simplest and fastest  SELECT c   FROM   comments c JOIN   unnest   1 3 2 4    int    WITH ORDINALITY t id  ord  USING  id  ORDER  BY t ord    WITH ORDINALITY was introduced with in Postgres 9 4   No need for a subquery  we can use the set-returning function like a table directly   A k a   quot table-function quot     A string literal to hand in the array instead of an ARRAY constructor may be easier to implement with some clients   For convenience  optionally   copy the column name we are joining to  id in the example   so we can join with a short USING clause to only get a single instance of the join column in the result    Detailed explanation   PostgreSQL unnest   with element number

User · Answer

Slight improvement over the version that uses a sequence I think   CREATE OR REPLACE FUNCTION in sort anyarray  out id anyelement  out ordinal int  LANGUAGE SQL AS        SELECT  1 i   i FROM generate series array lower  1 1  array upper  1 1   i       SELECT         FROM      comments c     INNER JOIN  SELECT   FROM in sort ARRAY 1 3 2 4    AS in sort         USING  id  ORDER BY in sort ordinal

User · Answer

You can do it quite easily with  introduced in PostgreSQL 8 2  VALUES          Syntax will be like this   select c   from comments c join     values      1 1        3 2        2 3        4 4    as x  id  ordering  on c id   x id order by x ordering

User · Answer

And here s another solution that works and uses a constant table  http   www postgresql org docs 8 3 interactive sql-values html    SELECT   FROM comments AS c   VALUES  1 1   3 2   2 3   4 4    AS t  ord id ord  WHERE  c id IN  1 3 2 4   AND  c id   t ord id  ORDER BY ord   But again I m not sure that this is performant   I ve got a bunch of answers now  Can I get some voting and comments so I know which is the winner   Thanks All  -

User · Answer

To do this  I think you should probably have an additional  ORDER  table which defines the  mapping of IDs to order  effectively doing what your response to your own question said   which you can then use as an additional column on your select which you can then sort on   In that way  you explicitly describe the ordering you desire in the database  where it should be

User · Answer

create sequence serial start 1   select   from comments c join  select unnest ARRAY 1 3 2 4   as id  nextval  serial   as id sorter  x on x id   c id order by x id sorter   drop sequence serial     EDIT   unnest is not yet built-in in 8 3  but you can create one yourself the beauty of any     create function unnest anyarray  returns setof anyelement language sql as        select  1 i  from generate series array lower  1 1  array upper  1 1   i        that function can work in any type   select unnest array  John   Paul   George   Ringo    as beatle select unnest array 1 3 2 4   as id

User · Answer

sans SEQUENCE  works only on 8 4   select   from comments c join        select id  row number   over   as id sorter       from  select unnest ARRAY 1 3 2 4   as id  as y   x on x id   c id order by x id sorter

User · Answer

Just because it is so difficult to find and it has to be spread  in mySQL this can be done much simpler  but I don t know if it works in other SQL   SELECT   FROM  comments  WHERE  comments   id  IN   12   5   3   17   ORDER BY FIELD  comments   id   12   5   3   17

User · Answer

Lets get a  visual impression about what was already  said  For example you have a table with some tasks   SELECT a id a status a description FROM minicloud tasks as a ORDER BY random      id     status       description     ---- ------------ ------------------   4   processing   work on postgres   6   deleted      need some rest   3   pending      garden party   5   completed    work on html   And you want to order the list of tasks by its status   The status is a list of string values    processing  pending   completed  deleted    The trick is to give each status value an interger and order the list numerical   SELECT a id a status a description FROM minicloud tasks AS a   JOIN       VALUES   processing   1     pending   2     completed   3     deleted   4      AS b  status  id  ON  a status   b status    ORDER BY b id ASC    Which leads to    id     status       description     ---- ------------ ------------------   4   processing   work on postgres   3   pending      garden party   5   completed    work on html   6   deleted      need some rest   Credit  user80168

User · Answer

I agree with all other posters that say  don t do that  or  SQL isn t good at that      If you want to sort by some facet of comments then add another integer column to one of your tables to hold your sort criteria and sort by that value   eg   ORDER BY comments sort DESC     If you want to sort these in a different order every time then    SQL won t be for you in this case

User · Answer

Another way to do it in Postgres would be to use the idx function   SELECT   FROM comments ORDER BY idx array 1 3 2 4   comments id    Don t forget to create the idx function first  as described here  http   wiki postgresql org wiki Array Index

User · Answer

In Postgresql  select   from comments where id in  1 3 2 4  order by position id  text in  1 3 2 4

[sql] ORDER BY the IN value list

Examples related to sql

Examples related to postgresql

Examples related to sql-order-by

Examples related to sql-in