ORDER BY a lista de valores IN

Eu tenho uma consulta SQL simples no PostgreSQL 8.3 que pega um monte de comentários. Eu forneço uma lista ordenada de valores para a construção IN na cláusula WHERE :

 SELECT * FROM comments WHERE (comments.id IN (1,3,2,4)); 

Isso retorna comentários em uma ordem arbitrária que, no meu caso, é ids como 1,2,3,4 .

Eu quero as linhas resultantes classificadas como a lista na construção IN : (1,3,2,4) .
Como conseguir isso?

   

Você pode fazê-lo facilmente com (introduzido no PostgreSQL 8.2) VALUES (), ().

A syntax será assim:

 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 

Só porque é tão difícil de encontrar e tem que ser espalhado: em mySQL isso pode ser feito de forma muito mais simples , mas não sei se funciona em outro SQL.

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

Eu acho que isso é melhor:

 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 

No Postgres 9.4 ou posterior, isso é provavelmente mais simples e rápido :

 SELECT c.* FROM comments c JOIN unnest('{1,3,2,4}'::int[]) WITH ORDINALITY t(id, ord) USING (id) ORDER BY t.ord; 
  • Usando o novo WITH ORDINALITY , que @a_horse já mencionou .

  • Nós não precisamos de uma subconsulta, podemos usar a function set-returning como uma tabela.

  • Um literal de string para entregar a matriz em vez de um construtor ARRAY pode ser mais fácil de implementar com alguns clientes.

Explicação detalhada:

  • PostgreSQL unnest () com o número do elemento

Outra maneira de fazer isso no Postgres seria usar a function idx .

 SELECT * FROM comments ORDER BY idx(array[1,3,2,4], comments.id) 

Não esqueça de criar a function idx primeiro, como descrito aqui: http://wiki.postgresql.org/wiki/Array_Index

Com o Postgres 9.4 isto pode ser feito um pouco mais curto:

 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 

Removendo a necessidade de atribuir / manter manualmente uma posição para cada valor.

Com o Postgres 9.6 isso pode ser feito usando 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); 

O CTE é usado para que a lista de valores precise ser especificada apenas uma vez. Se isso não for importante, isso também pode ser escrito como:

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

No Postgresql:

 select * from comments where id in (1,3,2,4) order by position(id::text in '1,3,2,4') 

Ao pesquisar isso mais, encontrei esta solução:

 SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4)) ORDER BY CASE "comments"."id" WHEN 1 THEN 1 WHEN 3 THEN 2 WHEN 2 THEN 3 WHEN 4 THEN 4 END 

No entanto, isso parece bastante detalhado e pode ter problemas de desempenho com grandes conjuntos de dados. Alguém pode comentar sobre esses problemas?

Para fazer isso, acho que você provavelmente deveria ter uma tabela “ORDER” adicional que define o mapeamento de IDs para fazer o pedido (fazendo efetivamente o que sua resposta à sua própria pergunta), que você pode usar como uma coluna adicional em sua seleção que você pode então classificar.

Dessa forma, você descreve explicitamente a ordem desejada no database, onde deveria estar.

sans SEQUENCE, funciona apenas no 8.4:

 select * from comments c join ( select id, row_number() over() as id_sorter from (select unnest(ARRAY[1,3,2,4]) as id) as y ) x on x.id = c.id order by x.id_sorter 
 SELECT * FROM "comments" JOIN ( SELECT 1 as "id",1 as "order" UNION ALL SELECT 3,2 UNION ALL SELECT 2,3 UNION ALL SELECT 4,4 ) j ON "comments"."id" = j."id" ORDER BY j.ORDER 

ou se preferir o mal sobre o bem:

 SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4)) ORDER BY POSITION(','+"comments"."id"+',' IN ',1,3,2,4,') 

Concordo com todos os outros cartazes que dizem “não faça isso” ou “o SQL não é bom nisso”. Se você quiser ordenar por alguma faceta de comentários, adicione outra coluna inteira a uma de suas tabelas para manter seus critérios de sorting e classificar por esse valor. Por exemplo, “ORDER BY comments.sort DESC” Se você quiser classificá-los em uma ordem diferente toda vez, então … SQL não será para você neste caso.

E aqui está outra solução que funciona e usa uma tabela constante ( http://www.postgresql.org/docs/8.3/interactive/sql-values.html ):

 SELECT * FROM comments AS c, (VALUES (1,1),(3,2),(2,3),(4,4) ) AS t (ord_id,ord) WHERE (c.id IN (1,3,2,4)) AND (c.id = t.ord_id) ORDER BY ord 

Mas, novamente, não tenho certeza se isso é de bom desempenho.

Eu tenho um monte de respostas agora. Posso obter algumas votações e comentários para saber qual é o vencedor!

Obrigado a todos 🙂

 create sequence serial start 1; select * from comments c join (select unnest(ARRAY[1,3,2,4]) as id, nextval('serial') as id_sorter) x on x.id = c.id order by x.id_sorter; drop sequence serial; 

[EDITAR]

O unnest ainda não está embutido em 8.3, mas você pode criar um você mesmo (a beleza de qualquer *):

 create function unnest(anyarray) returns setof anyelement language sql as $$ select $1[i] from generate_series(array_lower($1,1),array_upper($1,1)) i; $$; 

essa function pode funcionar em qualquer tipo:

 select unnest(array['John','Paul','George','Ringo']) as beatle select unnest(array[1,3,2,4]) as id 

Ligeira melhoria na versão que usa uma sequência, penso:

 CREATE OR REPLACE FUNCTION in_sort(anyarray, out id anyelement, out ordinal int) LANGUAGE SQL AS $$ SELECT $1[i], i FROM generate_series(array_lower($1,1),array_upper($1,1)) i; $$; SELECT * FROM comments c INNER JOIN (SELECT * FROM in_sort(ARRAY[1,3,2,4])) AS in_sort USING (id) ORDER BY in_sort.ordinal; 
 select * from comments where comments.id in (select unnest(ids) from bbs where id=19795) order by array_position((select ids from bbs where id=19795),comments.id) 

aqui, [bbs] é a tabela principal que possui um campo chamado ids e ids é a matriz que armazena o comments.id.

passou no postgresql 9.6