PostgreSQL order of WHERE "table"."column" IN query

Question

In PostgreSQL, does this query

SELECT "table".* FROM "table" WHERE "table"."column" IN (1, 5, 3)

always return the results in the 1, 5, 3 order or is it ambiguous?
If it's ambiguous, how do I properly assure the results are in the order 1, 5, 3?

@bernie - If this is a duplicate, please provide a link to what it is a duplicate of. — Brian, Apr 23 '13 at 23:35

score 3 · Answer 1 · answered Apr 23 '13 at 23:12

3

The WHERE clause will not order the results in any way, it will just select matching records, in whatever order the database index finds them in.

You'll have to add an order by clause.

answered Apr 23 '13 at 23:12

Greg Olsen

1,370
17
28

score 2 · Answer 2 · answered Apr 23 '13 at 23:19

2

False orders before true

SELECT "table".*
FROM "table"
WHERE "table"."column" IN (1, 5, 3)
order by
    "column" != 1,
    "column" != 5,
    "column" != 3

answered Apr 23 '13 at 23:19

Clodoaldo Neto

118,695
26
233
260

score 2 · Accepted Answer · answered Apr 23 '13 at 23:19

2

Add something like the following to your select statement

order by CASE WHEN "column"=1 THEN 1
              WHEN "column"=2 THEN 2
              ELSE 3
       END

if you have many more than three values it may be easier to make a lookup table and join to that in you query

answered Apr 23 '13 at 23:19

Ian Kenney

6,376
1
25
44

I have about 20 values, but I was able to implement this solution nicely. Thanks! – McFadden Apr 29 '13 at 21:52

score 1 · Answer 4 · answered Apr 23 '13 at 23:23

1

When you use "IN" in a select statement Postgre just return the matching rows against that range of values. It's not ambiguous in any way but if you need to order the results you need to explicitly add ORDER BY "column1,column2..."

answered Apr 23 '13 at 23:23

Edgar Sampere

263
4
24

score 1 · Answer 5 · edited May 23 '17 at 10:30

A set knows no order per se. A SELECT query needs ORDER BY to return ordered rows.

Other answers have suggested CASE statements or boolean expressions in your ORDER BY, but that's far from elegant and rather inefficient with big tables. I suggest to use an array or a comma-separated string instead of a set for your query.

For a given table:

CREATE TABLE tbl (col int);

Using an array it can work like this:

SELECT col
FROM   tbl
JOIN  (
   SELECT col, row_number() OVER () AS rn
   FROM   unnest('{1,5,3}'::int[]) AS col
   ) u USING (col)
ORDER  BY rn;

Returns all rows found in the sequence of the input array:

-> SQLfiddle

For more details and future-proof code consider this closely related question:
PostgreSQL unnest() with element number

Or the corresponding question on dba.SE:
How to preserve the original order of elements in an unnested array?

PostgreSQL order of WHERE "table"."column" IN query

5 Answers5