ORDER BY the IN value list

问题:

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?

回答1:

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

回答2:

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\')

回答3:

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

回答4:

In Postgres 9.4 or later, this is probably 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;

• Using the new WITH ORDINALITY, that @a_horse already mentioned.

• We don\'t need a subquery, we can use the set-returning function like a table.

• A string literal to hand in the array instead of an ARRAY constructor may be easier to implement with some clients.

Detailed explanation:

• PostgreSQL unnest() with element number

回答5:

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

回答6:

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

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);

回答7:

In Postgresql:

select *
from comments
where id in (1,3,2,4)
order by position(id::text in \'1,3,2,4\')

回答8:

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?

回答9:

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.

回答10:

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

回答11:

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,\')

回答12:

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.

回答13:

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 :-)

回答14:

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

回答15:

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;

回答16:

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