Trier PAR la liste des valeurs de l'IN

Question

J'ai une requête SQL simple dans PostgreSQL 8.3 qui récupère un tas de commentaires. Je fournis une liste triée de valeurs à la construction IN dans la clause WHERE:

SÉLECTIONNER * FROM comments WHERE (comments.id IN (1,3,2,4));

Cela renvoie des commentaires dans un ordre arbitraire qui, dans mon cas, sont des identifiants comme 1,2,3,4.

Je veux que les lignes résultantes soient triées comme la liste dans la construction IN: (1,3,2,4).
Comment y parvenir?

Answer 1

Vous pouvez le faire assez facilement avec (introduit dans PostgreSQL 8.2) VALUES (), ().

La syntaxe sera la suivante:

select c.*
from comments c
join (
  values
    (1,1),
    (3,2),
    (2,3),
    (4,4)
) as x (id, ordering) on c.id = x.id
order by x.ordering

Answer 2

Utilisez WITH ORDINALITY dans Postgres 9.4 ou ultérieur.

SELECT c.*
FROM   comments c
JOIN   unnest('{1,3,2,4}'::int[]) WITH ORDINALITY t(id, ord) USING (id)
ORDER  BY t.ord;

• Pas besoin d'une sous-requête, nous pouvons utiliser la fonction retournant un ensemble directement comme une table - aussi appelée "table-function".

• Un littéral de chaîne pour passer le tableau au lieu d'un constructeur ARRAY peut être plus facile à implémenter avec certains clients.

• Pour plus de commodité (facultatif), faites correspondre le nom de colonne avec lequel nous faisons la jointure ("id" dans l'exemple), afin que nous puissions joindre avec une courte clause USING et obtenir une seule instance de la colonne de jointure dans le résultat.

• Fonctionne avec n'importe quel type d'entrée. Si votre colonne de clé est de type text, fournissez quelque chose comme '{foo,bar,baz}'::text[].

Explication détaillée:

• PostgreSQL unnest() with element number

Answer 3

Juste parce qu'il est si difficile à trouver et il doit être diffusé : dans MySQL cela peut être fait beaucoup plus simplement, mais je ne sais pas si cela fonctionne dans d'autres SQL.

SELECT * FROM `comments`
WHERE `comments`.`id` IN ('12','5','3','17')
ORDER BY FIELD(`comments`.`id`,'12','5','3','17')

Answer 4

Avec Postgres 9.4, cela peut être fait un peu plus court:

select c.*
from comments c
join (
  select *
  from unnest(array[43,47,42]) with ordinality
) as x (id, ordering) on c.id = x.id
order by x.ordering;

Ou un peu plus compact sans une table dérivée:

select c.*
from comments c
  join unnest(array[43,47,42]) with ordinality as x (id, ordering) 
    on c.id = x.id
order by x.ordering

Supprimant le besoin d'attribuer/maintenir manuellement une position à chaque valeur.

Avec Postgres 9.6, cela peut être fait en utilisant array_position():

with x (id_list) as (
  values (array[42,48,43])
)
select c.*
from comments c, x
where id = any (x.id_list)
order by array_position(x.id_list, c.id);

Le CTE est utilisé afin que la liste de valeurs ne soit spécifiée qu'une seule fois. Si cela n'est pas important, cela peut également être écrit comme:

select c.*
from comments c
where id in (42,48,43)
order by array_position(array[42,48,43], c.id);

Answer 5

Je pense que cette façon est meilleure :

SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4))
    ORDER BY  id=1 DESC, id=3 DESC, id=2 DESC, id=4 DESC