I have a table

Current query

SELECT DISTINCT ON ("senderId", "receiverId") "sender"."id" AS "senderId", "receiver"."id" AS "receiverId", cm."createdAt" AS "createdAt" FROM "chat_message" "cm" LEFT JOIN "user" "sender" ON "sender"."id"="cm"."senderId"  LEFT JOIN "user" "receiver" ON "receiver"."id"="cm"."receiverId" WHERE "senderId" = 2 OR "receiverId" = 2 ORDER BY "senderId", "receiverId" ASC, cm."createdAt" DESC

Current result

Expected result

So, I need to select only one row in this pair where the date is bigger

CodePudding user response：

We can use a LEAST/GREATEST trick here along with DISTINCT ON:

SELECT DISTINCT ON (LEAST(senderId, receiverId),
                    GREATEST(senderId, receiverId))
       LEAST(senderId, receiverId) AS senderId,
       GREATEST(senderId, receiverId) AS receiverId,
       createdAt
FROM chat_message
ORDER BY 1, 2, createdAt DESC;