SQL: group by from other table and invert result

Question

I have some problem with my SQL query I have table message and table recipient

message is

ID    author    date
--------------------
0        1      2013-07-08 05:38:47
1        1      2013-07-13 05:38:47
2        1      2013-07-15 05:38:47
3        1      2013-07-15 05:38:47
4        2      2013-07-17 05:38:47
5        1      2013-07-28 05:38:47

recipient is

ID    m_id    recipient
--------------------
0        0      2
1        1      2
2        2      3
3        3      2
4        4      1
5        5      2

I need return rows from table message with group by recipient column from table recipient with last date in message table I'll try this

SELECT m.* 
FROM message as m
INNER JOIN recipient as r ON (m.ID = r.m_id)
WHERE m.author = 1
GROUP BY r.recipient
ORDER BY m.ID DESC

return is

ID    author    date
--------------------
2        1      2013-07-15 05:38:47
0        1      2013-07-08 05:38:47

but i need

ID    author    date
--------------------
5        1      2013-07-28 05:38:47
2        1      2013-07-15 05:38:47

please help

I USE MySQL Server 5.1

I found a solution to my problem

SELECT m.* 
FROM (
SELECT * FROM recipient 
    WHERE 1=1
    ORDER BY recipient.ID DESC
) AS r
INNER JOIN message AS m ON (r.m_id = m.ID)
WHERE m.author = 1
GROUP BY r.recipient

just reverse table recipient

**Missing**: Your RDBMS and version. Table definitions including NOT NULL, FK and PK constraints. How to break ties? What to do with missing recipients (if possible)? — Erwin Brandstetter, Aug 10 '13 at 18:18
Your test case is contradictory in multiple places. I assume this line in message `2 2 3` should read `2 2 1` and the result should be `4 2 2013-07-17 05:38:47`, `5 1 2013-07-28 05:38:47` according to your description. — Erwin Brandstetter, Aug 10 '13 at 18:32

score 2 · Accepted Answer · edited May 23 '17 at 12:11

Very simple and fast in PostgreSQL with DISTINCT ON - not standard SQL so not available in every RDBMS.

The question doesn't mention it, but deriving from the code examples it is actually looking for the row with the "last date" for each recipient for a given author.

SELECT DISTINCT ON (r.recipient)  m.*
FROM   message   m
JOIN   recipient r ON r.m_id = m.id
WHERE  m.author = 1
ORDER  BY r.recipient, m.date DESC, r.m_id -- to break ties

Details as well as multiple SQL standard alternatives here:
Select first row in each GROUP BY group?

Another solution with basic, standard SQL. Works with every major RDBMS, including MySQL (since that tag has been added):

SELECT m.* 
FROM   message   m
JOIN   recipient r ON r.m_id = m.id
WHERE  m.author = 1
AND NOT EXISTS (
   SELECT 1
   FROM   message   m1
   JOIN   recipient r1 ON r1.m_id = m1.id
   WHERE  r1.recipient = r.recipient 
   AND    m1.author = 1
   AND    m1.date > m.date
   )

Only the row with the latest date passes the NOT EXISTS anti-semi-join.

score 1 · Answer 2 · answered Aug 10 '13 at 18:02

Presuming you're using SQL-Server, you can use a ranking function like ROW_NUMBER:

WITH CTE AS(
    SELECT m.*,
      RN=ROW_NUMBER()OVER(PARTITION BY r.recipient ORDER BY  m.date DESC)
    FROM message as m
    INNER JOIN recipient as r ON (m.ID = r.m_id)
    WHERE m.author = 1
)
SELECT * FROM CTE WHERE RN = 1

Demo

SQL: group by from other table and invert result

2 Answers2

Linked