Postgresql 11 - Order by integer array index

Question

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 ?

Gordon Linoff · Accepted Answer · 2020-01-05 18:06:38Z

5

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);

answered Jan 5, 2020 at 18:06

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CalMlynarczyk Over a year ago

Note that this method doesn't work if your array is unsorted and contains duplicate values. For example, [1, 2, 1] would get ordered as [1, 1, 2].

GMB · Accepted Answer · 2020-01-05 14:26:17Z

5

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;

answered Jan 5, 2020 at 14:26

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

Postgresql 11 - Order by integer array index

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related