PostgreSQL: WHERE id = ANY (idsArray) - sort results by idsArray

user_1319163 0
Viewed 306

Is there a trick I'm missing when using a WHERE...ANY style query in Postgres to order the results by the given array? ie:

SELECT *
FROM table
WHERE id = ANY (<idsInDesiredOrder>)
postgresql database
3 Answers

Perhaps array_position can help you: 

WITH sample (id, description) AS (
    VALUES 
        (1, 'lorem ipsum'),
        (2, 'lorem ipsum'),
        (3, 'lorem ipsum'),
        (4, 'lorem ipsum')
)
SELECT 
    * 
FROM 
    sample 
WHERE 
    id = ANY (ARRAY[3,2]) 
ORDER BY 
    array_position(ARRAY[3,2], id);
user_79253660

As Marc mentioned, SQL will never guarantee ordering without an ORDER BY clause. Historically, cases like this were problematic in Postgres, because you couldn't write an ORDER BY which referenced the array index.

They addressed this in 9.4 by adding the WITH ORDINALITY clause for set-returning function calls, allowing you to unpack an array into a "values" column and an "index" column:

SELECT * FROM table
JOIN UNNEST(<preSortedIds>) WITH ORDINALITY u(id,pos) USING (id)
ORDER BY pos
user_11049790

In SQL, when you want to order a result use, you must use an "ORDER BY" clause. Else the results are in a inpredictable order. So rewrite you query as

SELECT *
FROM table
WHERE id = ANY (<preSortedIds>)
ORDER BY id
user_24203010
Related