CODEWITHSUNDEEP
sqlpostgresql
Vin.AI
Vin.AI

Reputation: 2437

PostgreSQL fetch record as in order passed for IN condition

Want result in same order as passed to IN condition:

SELECT "id", 
       "field_a", 
       "field_b", 
       To_timestamp("on_dated") 
FROM   "test" 
WHERE  "id" IN ( 3, 1, 6, 2, 4 );

Getting:

id    | field_a | field_b | on_dated
-------------------------------------
1     | Vinay   | M       | 1383224064
2     | Asha    | F       | 1383224064
3     | Shashi  | F       | 1383224064
4     | Vinita  | F       | 1383224064
5     | Arnav   | M       | 1383224064
6     | Jayant  | M       | 1383224064

Expecting:

id    | field_a | field_b | on_dated
-------------------------------------
3     | Shashi  | F       | 1383224064
1     | Vinay   | M       | 1383224064
6     | Jayant  | M       | 1383224064
2     | Asha    | F       | 1383224064
4     | Vinita  | F       | 1383224064

Tried:

SELECT "id", 
       "field_a", 
       "field_b", 
       To_timestamp("on_dated") 
FROM   "test" 
WHERE  "id" IN ( 3, 1, 6, 2, 4 ) 
ORDER  BY Field("id", '3', '1', '6', '2', '4');

But throws error :(

Found a better answer:

Also I found a more compact answer for this:

SELECT * FROM "test"
WHERE "id" IN (3,1,6,2,4)
ORDER BY (id=3, id=1, id=6, id=2, id=4) DESC;

Upvotes: 1

Views: 113

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659307

A set has no order by definition. So your original question cannot be answered. It only works by supplying additional information (like you do in your added solution).

You can pass an array as provided by @a_horse. I would advice to use generate_subscripts() in this case like demonstrated here:
PostgreSQL unnest() with element number

There is a new feature in the upcoming Postgres 9.4 (in development now) that makes this a lot simpler: WITH ORDINALITY. Details in the same answer.

For now, you can use a set of composite types including row numbers: 

SELECT t.*
FROM   test t
JOIN  (
    VALUES 
     (1, 3)
    ,(2, 1)
    ,(3, 6)
    ,(4, 2)
    ,(5, 5)
    ) val(rnk, test_id) USING (test_id)
ORDER  BY val.rnk;

->SQLfiddle demo.

Upvotes: 1

user330315
user330315

Reputation:

The only way I can think of is something like this:

with numbers (id, sort_order) as (
  values 
     (3,1), 
     (1,2),
     (6,3),
     (2,4), 
     (4,5)
)
SELECT t.id, 
       t.field_a, 
       t.field_b, 
       to_timestamp(t.on_dated) 
FROM test
  JOIN numbers n on t.id = n.id
ORDER BY n.sort_order;

Another slightly more compact version:

with numbers (id, sort_order) as (
  select i, 
         row_number() over () 
  from unnest(ARRAY[3,1,6,2,4]) i
)
SELECT t.id, 
       t.field_a, 
       t.field_b, 
       to_timestamp(t.on_dated) 
FROM test
  JOIN numbers n on t.id = n.id
ORDER BY n.sort_order;

But: this relies on the fact that unnest always produces the rows in the same order. I'm not entirely sure this is always the case.

If you need this functionality very often, you can create a function to do that:

create or replace function get_index(to_find integer, elements int[])
  returns integer
as
$$
declare
  idx integer;
  x integer;
begin
  idx := 1;
  foreach x in array elements
  loop
    if to_find = x then
      return idx;
    end if;
    idx := idx + 1;
  end loop;
  return -1;
end;
$$
language plpgsql;

Then you can write:

SELECT id, 
       field_a, 
       field_b, 
       to_timestamp(on_dated) 
FROM test
WHERE id = any (array[2,7,3,1])
ORDER BY get_index(id, array[2,7,3,1]);

Upvotes: 1

Related Questions