CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
Lilleman
Lilleman

Reputation: 8121

How do I sort by a specific order

Table users:

id | firstname | lastname
---+-----------+---------
 1 | John      | Smith
 2 | Adam      | Tensta
 3 | Anna      | Johansson

I want to select these in the order of ID 2, 3, 1. ONLY specified by the id-field. Is this possible?

I'm thinking something like SELECT * FROM users ORDER BY id ORDER(2,3,1)

Can this be done, and in that case how?

Upvotes: 13

Views: 15416

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

Generic solution for Postgres 9.4 or later

For any number of values. Just pass an array of matching type with your preferred sort order:

SELECT u.*
FROM   users u
LEFT   JOIN unnest('{2,3,1}'::int[]) WITH ORDINALITY o(id, ord) USING (id)
ORDER  BY o.ord;

This sorts rows without match last because ord is NULL then and that sorts last in ascending order.

Similar solutions possible for older versions without ORDINALITY.

Related:

Original answer for the given simple case

SELECT * FROM users ORDER BY (id+1)%3

% is the modulo operator.

Upvotes: 1

user359040
user359040

Reputation:

Should work with a CASE in the order by:

SELECT * 
FROM users 
ORDER BY case id when 2 then 1
                 when 3 then 2
                 when 1 then 3
         end

Upvotes: 21

Related Questions