No sabía muy bien cómo describir esto, así que disculpe el título...
Estoy tratando de escribir una función para recuperar el mensaje más reciente que pertenece a una conversación, cuando se me proporciona una serie de ID de conversación.
Mi tabla de "mensajes" se ve así:
id | conversationId | body | createdDate ------------------------------------------------------- 1 | 1 | Hello | 2020-06-09 01:01 2 | 1 | How are you? | 2020-06-09 01:02 3 | 2 | Hi | 2020-06-09 01:02 4 | 1 | I'm good, you? | 2020-06-09 01:03 5 | 2 | Hey there! | 2020-06-09 01:04Llegué a consultar:
SELECT * FROM "message" WHERE "conversationId" IN (1, 2) ORDER BY "createdDate" DESC
Como era de esperar, esto devuelve todos los mensajes, que coinciden con la identificación de la conversación proporcionada, ordenados por la fecha de createdDate más reciente.
Estoy un poco confundido sobre cómo puedo LIMIT para incluir solo el primer resultado (fecha de createdDate más reciente) para cada ID de conversationId .
La salida que estoy buscando sería:
id | conversationId | body | createdDate ------------------------------------------------------- 4 | 1 | I'm good, you? | 2020-06-09 01:03 5 | 2 | Hey there! | 2020-06-09 01:04Ese es un problema típico de mayor n por grupo. En Postgres, una forma simple y eficiente de abordar esto es distinct on :
select distinct on (conversationId) m.* from messages m -- where conversationId in (...) -- if needed order by conversationId, createdDate desctambién puede usar la función de ventana row_number
select id, conversationId, body, createdDate from ( SELECT *, row_number() over (partition by conversationId order by createdDate desc) as rn FROM "message" ) val where rnk = 1como se indicó anteriormente en otras respuestas, DISTINCT ON sería la forma más fácil. pero viene con restricciones. especialmente con orden de clasificación.
sin restricciones, esto podría lograrse con estos 2 métodos, también sin usar un GRUPO POR
A) solo un simple ANTI JOIN;
SELECT m.* FROM "message" AS m LEFT JOIN "message" _m ON _m."conversationId" = m."conversationId" AND _m."createdDate" > m."createdDate" WHERE _m."id" IS NULLse llama ANTI JOIN, porque normalmente queremos los resultados "unidos" y en este caso no los queremos. así que haga una unión externa autoizquierda con los criterios requeridos, que es la marca de tiempo más alta en la conversación.
Para comprender mejor este concepto, puede ejecutar la consulta sin la condición _m."id" IS NULL. esto le dará todos los registros coincidentes:
Right Side : "m" || Left Side : "_m" --------------------------------------------------------------------------------- id | conversationId | createdDate || id | conversationId | createdDate --------------------------------------------------------------------------------- 1 | 1 | 2020-06-09 01:01 || 2 | 1 | 2020-06-09 01:02 1 | 1 | 2020-06-09 01:01 || 4 | 1 | 2020-06-09 01:03 2 | 1 | 2020-06-09 01:02 || 4 | 1 | 2020-06-09 01:03 3 | 2 | 2020-06-09 01:02 || 5 | 2 | 2020-06-09 01:04 4 | 1 | 2020-06-09 01:03 || NULL 5 | 2 | 2020-06-09 01:04 || NULLlos registros con 4 y 5 no tienen coincidencias, porque no hay fechas superiores en esas conversaciones. cuando no hay un registro coincidente para el lado izquierdo, podemos estar seguros de que el lado derecho tiene la marca de tiempo más alta para esa conversación.
B) una simple SUBCONSULTA
SELECT m.* FROM "message" m WHERE m."id" = ( SELECT "id" FROM "message" WHERE "conversationId" = m."conversationId" ORDER BY "createdDate" DESC LIMIT 1 )la SUBCONSULTA en la condición siempre devuelve la identificación del registro con la marca de tiempo más alta en la conversación. usamos su valor de retorno para limitar los registros en la consulta principal.