How do I sort by a specific order

Question

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?

Is it just a coincidence that the order you are trying to achieve is alphabetical by first name? Or is there some other indicator in the data to give you the order you desire? Sorting arbitrarily by ID seems odd. — D Stanley, Jan 03 '12 at 14:14
It is just a coincidence, the idea is to sort based on the ID only. This can be for several reasons. For example user-specified order, ordered by another resultset on another layer in the application than the database etc. — Lilleman, Jan 05 '12 at 20:38

score 21 · Accepted Answer · answered Jan 03 '12 at 14:01

21

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

answered Jan 03 '12 at 14:01

Nice solution. Just tested it on Oracle, not sure if it works with Postgres. – Jules Jan 03 '12 at 14:04
4

@Jules: should work in any database. That is ANSI compliant SQL – Jan 03 '12 at 18:50

Erwin Brandstetter · Answer 2 · 2017-12-16T17:18:06.287

1

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.

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

edited Dec 16 '17 at 17:18

answered Jan 03 '12 at 15:33

This is valid on this data set, but I want to be able to sort on any given order on the ID column. Interesting solution tho. :) – Lilleman Jan 05 '12 at 20:43
@Lilleman: I know the question is old, but when stumbling across it just now, I couldn't but add a generic solution. – Erwin Brandstetter Dec 16 '17 at 17:21
@ErwinBrandstetter Thanks so much for editing this - the generic solution you added was just what I needed. :) – Nathan Long Sep 23 '19 at 18:37