Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know that the order of returned records is not guaranteed with the IN statement for Pstgres. For example if I do this:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)

I may get this result:

16674
13588
13589

However, I want the records returned to respect the in statement input, so I found few solutions online, such as:

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)
ORDER BY POSITION(id::text in '(13589, 16674, 13588)')

or

SELECT "users"."id" FROM "users" WHERE "users"."id" IN (13589, 16674, 13588)
ORDER BY id = 13589 desc,
         id = 16674 desc,
         id = 13588 desc;

I wonder if there is a nicer way to do this, or better yet more efficient?

share|improve this question
    
"Nicer" is quite subjective; would you consider using ORDER BY CASE WHEN ... THEN ... as nicer ? As to efficiency, I'm pretty sure optimizer will generate the same plan. –  a1ex07 Feb 22 at 5:18

2 Answers 2

up vote 4 down vote accepted

WITH ORDINALITY in Postgres 9.4+

You will be most interested in the new feature WITH ORDINALITY in the upcoming version 9.4 (devel status as of writing). Per documentation:

When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the output which starts from 1 and increments by 1 for each row of the function's output. This is most useful in the case of set returning functions such as unnest().

SELECT *
FROM   unnest('{13589, 16674, 13588}'::int[]) WITH ORDINALITY AS r(some_id, rn)

array or set?

x IN (set) statements are rewritten internally in Postgres to x = ANY (array), which is equivalent:

SELECT users.id FROM users WHERE users.id = ANY ('{13589, 16674, 13588}')

You can see for yourself with EXPLAIN.

Postgres 9.3 or earlier

For now, to preserve the order of elements, you could:

SELECT y.id
FROM  (
   SELECT x.arr[x.rn] AS id, x.rn
   FROM   (
      SELECT arr, generate_subscripts(arr, 1) AS rn
      FROM  (SELECT '{13589, 16674, 13588}'::int[]) t(arr)
      ) x
   ) y
JOIN   users u USING (id)
ORDER  BY rn;

-> SQLfiddle

More details under this closely related question:
How to preserve the original order of elements in an unnested array?

share|improve this answer
    
thanks for you answer. I get this error when I run the query for postgres 9.3 and earlier ERROR: missing FROM-clause entry for table "x" LINE 1: SELECT x.id –  Gandalf StormCrow Feb 24 at 17:11
    
@G: sorry, y.id, not x.id. Added a fiddle. –  Erwin Brandstetter Feb 24 at 17:13
    
thanks, works not awesome –  Gandalf StormCrow Feb 24 at 17:16
    
@GandalfStormCrow: Not awesome? –  Erwin Brandstetter Feb 24 at 17:21
    
I meant to say thats awesome, I'm not sure where not came from, maybe someone is doing prank on me, didn't lock my computer and this answer was on the screen –  Gandalf StormCrow Feb 24 at 20:10

If you want to rewrite the condition slightly you could do something like this:

with id_list (id, sort_order) as (
   values 
      (13589, 1), 
      (16674, 2),
      (13588, 3)
)
select u.id
from users u
  join id_list il on il.id = u.id
order by il.sort_order;

You can also put the values clause directly into the join:

select u.id
from users u
  join (
   values 
      (13589, 1), 
      (16674, 2),
      (13588, 3)
  ) as il(id, sort_order) on il.id = u.id
order by il.sort_order;
share|improve this answer
2  
@ErwinBrandstetter's method has the nice advantage that it can use an array of integers as a bind variable from a host language. –  Colin 't Hart Feb 22 at 16:01
    
@Colin'tHart: yes, absolutely. I just wanted to show a different way of doing this. –  a_horse_with_no_name Feb 24 at 17:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.