sql - postgres - PEDIDO POR la lista de valores IN

select postgres where

Question

Tengo una consulta SQL simple en PostgreSQL 8.3 que capta un montón de comentarios. Proporciono una lista ordenada de valores para la construcción IN en la cláusula WHERE :

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

Esto devuelve comentarios en un orden arbitrario que en mi caso son identificadores como 1,2,3,4 .

Quiero que las filas resultantes se (1,3,2,4) como la lista en la construcción IN : (1,3,2,4) .
¿Cómo lograr eso?

Answer 1

Al investigar esto un poco más encontré esta solución:

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

Sin embargo, esto parece bastante detallado y puede tener problemas de rendimiento con grandes conjuntos de datos. ¿Alguien puede comentar sobre estos problemas?

Answer 2

Con Postgres 9.4 esto puede hacerse un poco más corto:

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

Eliminando la necesidad de asignar / mantener manualmente una posición para cada valor.

Con Postgres 9.6 esto puede hacerse 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);

El CTE se usa para que la lista de valores solo deba especificarse una vez. Si eso no es importante, esto también se puede escribir como:

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

Answer 3

Creo que de esta manera es mejor:

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

Answer 4

El hecho de que sea tan difícil de encontrar y tenga que difundirse: en mySQL esto se puede hacer mucho más simple , pero no sé si funciona en otro SQL.

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

Answer 5

En Postgres 9.4 o posterior, esto es probablemente el más simple y 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 el nuevo WITH ORDINALITY , ese @a_horse ya mencionado .

• No necesitamos una subconsulta, podemos utilizar la función de devolución de conjuntos como una tabla.

• Un string literal para entregar en la matriz en lugar de un constructor ARRAY puede ser más fácil de implementar con algunos clientes.

Explicación detallada:

• PostgreSQL unnest () con número de elemento

Answer 6

En Postgresql:

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

Answer 7

Estoy de acuerdo con todos los demás carteles que dicen "no hagas eso" o "SQL no es bueno para eso". Si desea ordenar por alguna faceta de comentarios, agregue otra columna entera a una de sus tablas para mantener sus criterios de clasificación y ordenar por ese valor. por ejemplo, "ORDER BY comments.sort DESC" Si desea ordenar estos en un orden diferente cada vez, entonces ... SQL no será para usted en este caso.

Answer 8

Ligera mejora sobre la versión que usa una secuencia, creo:

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;

Answer 9

Otra forma de hacerlo en Postgres sería usar la función idx .

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

No olvide crear primero la función idx , como se describe aquí: http://wiki.postgresql.org/wiki/Array_Index

Answer 10

Para hacer esto, creo que probablemente debería tener una tabla adicional "ORDEN" que defina el mapeo de los ID a ordenar (haciendo efectivamente lo que dice su respuesta a su propia pregunta), que luego puede usar como una columna adicional en su selección, que luego puedes ordenar

De esa manera, describe explícitamente el orden que desea en la base de datos, donde debería estar.

Answer 11

Puedes hacerlo bastante fácilmente con (introducido en PostgreSQL 8.2) VALUES (), ().

La sintaxis será así:

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 12

Y aquí hay otra solución que funciona y usa una tabla 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

Pero nuevamente, no estoy seguro de que esto esté funcionando.

Tengo un montón de respuestas ahora. ¿Puedo obtener votos y comentarios para saber cuál es el ganador?

Gracias a todos :-)

Answer 13

sans SEQUENCE, solo funciona en 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

Answer 14

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

o si prefieres el mal sobre el bien:

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

Answer 15

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]

Unnest aún no está incorporado en 8.3, pero puede crear uno usted mismo (la belleza de cualquier *):

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

esa función puede funcionar en cualquier tipo:

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

Answer 16

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)

aquí, [bbs] es la tabla principal que tiene un campo llamado ids, y, ids es la matriz que almacena los comentarios.id.

pasado en postgresql 9.6