Как можно задать порядок результата

Перевод How to order by some random - query defined - values? с select * from depesz;

Представим простую ситуацию - есть таблица каких-то объектов (каждый со своим id) откуда надо достать объекты с id 3, 71, 5 и 16. И, самое главное, в том же порядке!

Как это сделать?

Во-первых, добавим тестовых данных:

CREATE TABLE test_data (
id INT4 PRIMARY KEY,
codename TEXT
);
INSERT INTO test_data ( id, codename )
SELECT i, 'codename for: ' || i
FROM generate_series( 1, 100 ) i;

Теперь, чтобы получить эти 4 строки, можно сделать так:

SELECT * FROM test_data WHERE id IN (3, 71, 5, 16);
id | codename
----+------------------
3 | codename FOR: 3
5 | codename FOR: 5
16 | codename FOR: 16
71 | codename FOR: 71
(4 rows)

Но, никак нельзя добавить ничего в ORDER BY, чтобы получить их в порядке указанном в IN (...). Итак, что можно сделать. Один способ это использовать (доступные с 8.2) VALUES() следующим образом:

SELECT
t.*
FROM
test_data t
JOIN (
VALUES
(1, 3),
(2, 71),
(3, 5),
(4, 16)
) AS c (ordering, id) ON t.id = c.id
ORDER BY
c.ordering;
id | codename
----+------------------
3 | codename FOR: 3
71 | codename FOR: 71
5 | codename FOR: 5
16 | codename FOR: 16
(4 rows)

Всё очень хорошо, только надо указывать дополнительные данные, и их формат не очень хорошо выглядит. К счастью есть другой способ. С 8.4 у нас есть Window Functions и unnest(), т.ч. можно сделать так:

WITH ordered_want AS (
SELECT
id,
row_number() over () AS ordering
FROM
unnest( '{3,71,5,16}'::INT4[] ) AS id
)
SELECT
t.*
FROM
test_data t
JOIN ordered_want o ON t.id = o.id
ORDER BY
o.ordering;

Уже лучше. В этом случае подстановка id-ков осуществляется намного проще - просто укажите массив целых и всё.

Есть ещё один способ. Если не можете использовать Window Functions, то можно задействовать относительно старый трюк с упорядочиванием по position():

SELECT
t.*
FROM
test_data t
WHERE
t.id IN (3,71,5,16)
ORDER BY
position( ',' || t.id || ',' IN ',3,71,5,16,' );

Тут не забудьте указать пердшествующую и последующую запятые и добавьте их также вокруг t.id в ORDER BY.

Да, тут будут проблемы, если вы работаете с текстовыми id, но всёже один из вариантов.

Комментарии:

# AREK, 25 июня 2010

Если поставить intarray, то можно упростить последний запрос следующим образом:

SELECT *, idx(‘{3,71,5,16}’, id) AS rank
FROM test_data
WHERE id = ANY(‘{3,71,5,16}’)
ORDER BY rank;

Заметьте также, что вместо IN тут используется = ANY(). Это может быть проблемой для кода приложения, если вы не пишите запросы сами, а просто подставляете значения.

Что касается текстовых id, то можно написать функцию idx(text[], text) и использовать тот же запрос.