CODEWITHSUNDEEP
sqlarrayspostgresqlsql-order-bypostgresql-11
Kerem Çakır
Kerem Çakır

Reputation: 401

Postgresql 11 - Order by integer array index

I want to list my products in the database according to their id order in the array

My array

'{3,2,1}'::int[]

For example

SELECT id FROM product WHERE id = ANY ('{3,2,1}'::int[]);

This query gets the products with ordered by product id

|id|
|1 |
|2 |
|3 |

But i want to list my products ordered by index of the array ids. Like this :

|id|
|3 |
|2 |
|1 |

Is it possible to do this ? How can i do ?

Upvotes: 7

Views: 3433

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use array_position():

ORDER BY array_position('{3,2,1}'::int[], id)

If you didn't want to repeat the array twice:

select p.id
from product p join
     (values ('{3,2,1}'::int[])) v(ar)
     on p.id = any(v.ar)
order by array_position(v.ar, p.id);

Upvotes: 5

GMB
GMB

Reputation: 222482

You can unnest() the array with option WITH ORDINALITY to keep track of the index of each element, join it with the table and use the index as ORDER BY criteria:

SELECT p.id
FROM product AS p
INNER JOIN unnest('{3,2,1}'::int[]) WITH ORDINALITY AS a(id, nr) 
    ON p.id = a.id
ORDER BY a.nr;

Upvotes: 5

Related Questions