I didn't quite know how to describe this, so please excuse the title...
I'm trying to write a function to retrieve the most recent message belonging to a conversation, when provided with an array of conversation IDs.
My "message" table looks as follows:
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:04
I got as far as to query:
SELECT * FROM "message" WHERE "conversationId" IN (1, 2) ORDER BY "createdDate" DESC
As expected this returns all of the messages, which match the conversation id's provided, sorted by most recent createdDate.
I'm a little confused at how I can LIMIT to only include the first result (most recent createdDate) for each conversationId.
The output I'm looking for would be:
id | conversationId | body | createdDate
-------------------------------------------------------
4 | 1 | I'm good, you? | 2020-06-09 01:03
5 | 2 | Hey there! | 2020-06-09 01:04
That's a typical greatest-n-per-group problem. In Postgres, a simple and efficient way to address this is distinct on:
select distinct on (conversationId) m.*
from messages m
-- where conversationId in (...) -- if needed
order by conversationId, createdDate desc
you can also use window function 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 = 1
as stated before in other answers DISTINCT ON would be easiest way. but it comes with restrictions. especially with sorting order.
without restrictions, this could be achieved with these 2 methods, also without using a GROUP BY
A) just a simple self 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 NULL
it is called ANTI JOIN, because normally we want the "joined" results and in this case we don't want them. so do a self left outer join with the required criteria which is the highest timestamp in the conversation.
to better understand this concept, you can run the query without the _m."id" IS NULL condition. this will give you all the matching records:
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 || NULL
the records with 4 and 5 doesn't have any matches, because there are no higher dates in those conversations. when there is no matching record for the left side, we can be sure that right side has the highest timestamp for that conversation.
B) a simple SUBQUERY
SELECT
m.*
FROM
"message" m
WHERE
m."id" = (
SELECT
"id"
FROM
"message"
WHERE
"conversationId" = m."conversationId"
ORDER BY
"createdDate" DESC
LIMIT 1
)
the SUBQUERY in the condition always returns the id of the record with the highest timestamp in the conversation. we use its return value to limit the records in the main query.