CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
PoeHaH
PoeHaH

Reputation: 1936

SQL group messages in thread and get latest message

I have a table storing messages with a body, from ID, to ID and the datetime it's sent on:

ID | From | To | SendDate | Body
---------------------------------
1    10     20   [a date]    blabla
2    20     10   [a date]    some text
3    8      10   [a date]    some more text
4    10     2    [a date]    text

I would like to capture all 'threads' of user 10, but group if them together and only return the last message (max senddate)

I can't get much further than this:

select * from message where [to] = 10 or [From] = 10 order by senddate desc

This will give me all messages user 10 is involved in, so it will return the exact table above, but record 1 and 2 belong to the same 'thread' (record 2 is a reply on record 1), so I need to group them somehow together and only return the newest record (record 2).

What I want is:

ID | From | To | SendDate | Body
---------------------------------
2    20     10   [a date]    some text
3    8      10   [a date]    some more text
4    10     2    [a date]    text

How can I do that?

Upvotes: 2

Views: 750

Answers (2)

Kenta Kudo
Kenta Kudo

Reputation: 41

I had the same task and my answer is as follows,

SELECT ID, From, To, SendDate, Body
FROM (
    SELECT With, MAX(SendDate) as LatestDate
    FROM (
        SELECT
            CASE WHEN message.From={{ id }} THEN message.To
                 WHEN message.To={{ id }} THEN message.From END AS With,
            SendDate
        FROM message
        WHERE From={{ id }} OR To={{ id }}
    )
    GROUP BY With
) r
INNER JOIN message m ON (r.With = m.From OR r.With = m.To) AND r.LatestDate = m.SendDate
ORDER BY LatestDate DESC

The point is that it once drops the {{ id }} information in the most inner subquery. By doing that, it can be grouped by threads. After that, it calls select again to join data other than latest date.

The problem is that if there are the same timestamp messages in the table, two threads would be selected though they represents the same thread. I think it would not be a problem in the actual situation but it should be better to be solved.

I suppose this answer is not perfect and there would be a more efficient way of solving this problem. Please tell me if anyone know more elegant answers.

Upvotes: 1

David Isla
David Isla

Reputation: 629

If you want to group all messages between users 10 and 20 (isn't it?)...then you can "reorder" your From and To fields to facilitate to build your select.

This is a posible solution:

SELECT BOTTOM, TOP, MAX("SendDate")
FROM
    (SELECT CASE WHEN Message."To" < "From" THEN Message."To" 
                 WHEN Message."To" > "From" THEN Message."From" END as Bottom,
            CASE WHEN Message."To" > "From" THEN Message."To" 
                 WHEN Message."To" < "From" THEN Message."From" END as Top,
            Message."SendDate",
            Message.Body
    FROM
        -- These sentences are only for simulate your TABLE   
        (select 2 as "ID", 10 as "From", 20 as "To", sysdate as "SendDate", 'Text' as     Body from dual
        union
        select 3 as "ID", 20 as "From", 10 as "To", sysdate+1 as "SendDate", 'Text' as Body from dual
        union
        select 4 as "ID",  8 as "From", 10 as "To", sysdate as "SendDate", 'Text' as Body from dual) Messages) 
GROUP BY BOTTOM, TOP;

Upvotes: 0

Related Questions