registros - PostgreSQL no me permite agrupar una columna con orden

que es avg en sql

Question

En PostgreSQL quiero buscar a todos los usuarios a la vez y ordenarlos por fecha.

Esta es mi consulta:

SELECT id, useridx, isread, message, date
FROM messages
WHERE isread = 1
GROUP BY useridx
ORDER BY date DESC

Este es un ejemplo de datos :

------------------------------------------------------
+ id | useridx | isread | messsage | date +
------------------------------------------------------
1 | 1 | 0 | Hello | 2012-01-01
2 | 2 | 1 | Hi | 2012-01-02
3 | 3 | 1 | Test | 2012-01-03
4 | 3 | 0 | My Msg | 2012-01-04
5 | 4 | 1 | sadasd | 2012-01-05
6 | 4 | 1 | sdfsdfd | 2012-01-06
7 | 4 | 0 | sdfsdfsd | 2012-01-07
8 | 5 | 0 | 5345634 | 2012-01-08
9 | 6 | 0 | sdfdfsd | 2012-01-09
10 | 7 | 0 | sdfsdfsf | 2012-01-10
------------------------------------------------------

Ahora, lo que quiero hacer es buscar esta tabla agrupándolas a través de useridx y ordenar por fecha.

Resultado esperado :

------------------------------------------------------
+ id | useridx | isread | messsage | date +
------------------------------------------------------
6 | 4 | 1 | sdfsdfd | 2012-01-06
3 | 3 | 1 | Test | 2012-01-03
2 | 2 | 1 | Hi | 2012-01-02
------------------------------------------------------

Resultado actual

ERROR: column "messages.date" must appear in the GROUP BY clause or be used in an aggregate function

No quiero agrupar la fecha tampoco. Solo quiero agrupar con useridx y ordenarlos por fecha DESC.

¡Cualquier ayuda / idea es apreciada!

Nota: también intenté Distinct. No se ajusta a mis necesidades o lo hice mal.

Estoy muy confundido y atrapado entre los métodos DISTINCT ON y rank() .

Conclusión : para quienes tienen el mismo problema aquí pueden leer esto como una respuesta. Tanto @ kgrittn como @mu son respuestas demasiado cortas son correctas. Continuaré usando ambas respuestas y esquemas en mi proyecto y con el tiempo puedo entender cuál es la mejor, supongo. Por lo tanto, elija uno de ellos y continúe con su trabajo. Estaras bien.

Última actualización : a veces, Distinct On excluye algunos identificadores del resultado. Digamos que tengo una columna de identificación y tengo 6 filas que es lo mismo. Por lo tanto, distinto al excluirlo del resultado, PERO rank () simplemente lo da como resultado. Por lo tanto, use rank ()!

Answer 1

Desea utilizar la función de ventana rank() para ordenar los resultados dentro de cada grupo useridx y luego quitar el primero al envolver los resultados ordenados en una tabla derivada:

select id, useridx, isread, message, date
from (
select id, useridx, isread, message, date,
rank() over (partition by useridx order by date desc) as r
from messages
where isread = 1
) as dt
where r = 1

Eso le dará las filas con id 2, 3 y 6 de su muestra. Es posible que desee agregar una segunda clave de clasificación para realizar una elección consistente cuando tenga varios mensajes por useridx en la misma fecha.

Necesitará al menos PostgreSQL 8.4 (AFAIK) para tener funciones de ventana.

Answer 2

Estás agregando resultados.

Esto significa que en lugar de 2 filas para el usuario 3 , solo tendrá una fila. Pero también selecciona id , message , isread columns para la fila agregada. ¿Cómo se supone que PostgreSQL entrega estos datos? ¿Debería ser max() de posibles valores? Tal vez min() ?

Supongo que le gustaría tener los datos en los mensajes más nuevos. Prueba esta consulta:

SELECT id, useridx, isread, message, date FROM messages
WHERE isread = 1 AND (useridx, date) IN
(SELECT useridx, max(date) FROM messages WHERE isread = 1 GROUP BY useridx);

Answer 3

PostgreSQL, a diferencia de MySQL, no muestra datos aleatorios para las columnas que no se agregan en una consulta agregada.

Para una explicación más larga y más ejemplos: http://practiceovertheory.com/blog/2009/09/23/postgresql-s-group-by/

La solución está en el mensaje de error

ERROR: column "messages.date" must appear in the GROUP BY clause or be used in an aggregate function

Lo que significa que debe AGRUPAR por la columna "messages.date" o usar una función agregada como MIN () o MAX () cuando seleccione esta columna

Ejemplo:

SELECT MIN(id), useridx, isread, message, MAX(date)
FROM messages WHERE isread = 1
GROUP BY useridx, isread, message
ORDER BY MAX(date) DESC

Answer 4

Otra opción es usar SELECT DISTINCT ON (que es muy diferente de un simple SELECT DISTINCT ):

SELECT *
FROM (SELECT DISTINCT ON (useridx)
id, useridx, isread, message, date
FROM messages
WHERE isread = 1
ORDER BY useridx, date DESC) x
ORDER BY date DESC;

En algunos casos, esto puede escalarse mejor que los otros enfoques.

Answer 5

Años después, pero ¿no puedes pedir en la subconsulta FROM?

SELECT m.id, m.useridx, m.isread, m.message, m.date
FROM (
SELECT m2.id, m2.useridx, m2.isread, m2.message, m2.date
FROM message m2
ORDER BY m2.id ASC, m2.date DESC
) m
WHERE isread = 1
GROUP BY useridx

Esto funciona para mí en PostgreSQL 9.2