CODEWITHSUNDEEP
sql-server
JK9
JK9

Reputation: 380

SQL Server query SELECT only the latest message

 id|   from   | to      |message   |datetime             |
---| ---------|---------|----------|---------------------|
 1 | john     | peter   |Hi        |2016-01-01 12:00:00  |
 2 | peter    | john    |What's up |2016-01-01 12:01:00  |
 3 | jack     | jason   |Hey       |2016-01-02 12:00:00  |
 4 | jason    | jack    |Hi        |2016-01-01 12:01:00  |

Let's say I have a table like this, can you guys give me the idea how to write a SQL Server query to select only the latest message between two users.

The expected result for the table above should be select only id 2 and 3

Upvotes: 1

Views: 82

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

Here is an ANSI-92 compliant solution if you do not wish to use Felix's solution (which uses SQL Server ROW_NUMBER):

SELECT m1.*
FROM messages m1
INNER JOIN
(
    SELECT t.v1 AS from, t.v2 AS to, MAX(t.datetime) AS maxTime
    FROM
    (
        SELECT CASE WHEN from < to THEN from ELSE to END AS v1,
               CASE WHEN from < to THEN to ELSE from END AS v2,
               datetime
        FROM messages
    ) t
    GROUP BY t.v1, t.v2
) m2
ON ((m1.from = m2.from AND m1.to = m2.to) OR (m1.from = m2.to AND m1.to = m2.from))
    AND m1.datetime = m2.maxTime

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use ROW_NUMBER:

WITH Cte AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(
                            PARTITION BY 
                                CASE 
                                    WHEN [from] >= [to] THEN [from]
                                    ELSE [to]
                                END,
                                CASE 
                                    WHEN [from] >= [to] THEN [to]
                                    ELSE [from]
                                END
                            ORDER BY datetime DESC
                          )
    FROM tbl
)
SELECT * FROM CTE 
WHERE rn = 1
ORDER BY id

To achieve the proper grouping, I arrange the PARTITION BY alphabetically, meaning the first column in the PARTITION will be the first from from and to alphabetically, and the second column will be the last.

Try it here.

Upvotes: 1

Related Questions