SQL: filter a joined table

user856358 Published at Dev

user856358

I have two tables that have a one-many relationship, and I would like to put together a query that follows a rule to join a particular row in the 'many' table to a row in the 'one' table.

user table:

╔════╦══════════════╦
║ id ║  name        ║
╠════╬══════════════╬
║  1 ║ user 1       ║ 
║  2 ║ user 2       ║
║  3 ║ user 3       ║
║  4 ║ user 4       ║
╚════╩══════════════╩

Messages table:

╔════╦══════════════╦═══════════╦═════════╗
║ id ║  Text        ║ user_id   ║   date  ║
╠════╬══════════════╬═══════════╬═════════╣
║  1 ║ Hello        ║    1      ║  3/31   ║
║  2 ║ World        ║    1      ║  4/1    ║
║  3 ║ Test message ║    2      ║  4/2    ║
║  4 ║ Another test ║    3      ║  4/4    ║
╚════╩══════════════╩═══════════╩═════════╝

I am trying to perform a single join from user to messages to get the most recent message for the user. user 2 would have 'test message', user 3 would have 'another test'. User 1 is the one I cannot figure out - I would like to have one row for user 1 returned 'world', based on the fact that it has the most recent date, but I do not see a join that has the capability to perform filtering on a joined table.

Egor Lyah

Try something like this:

SELECT
    message_id
    , [user_id]
    , name
    , [Text]
    , [date]
FROM
(
SELECT
    M.id AS message_id
    , U.id AS [user_id]
    , name
    , [Text]
    , [date]
    --Rank rows for each users by date
    , RANK() OVER(PARTITION BY M.[user_id] ORDER BY [date] DESC, M.id DESC) AS Rnk
FROM
    @messages AS M
    INNER JOIN
    @users AS U
        ON M.[user_id] = U.id
) AS Tmp
WHERE
    --The latest date
    Tmp.Rnk  = 1

This code work in SQL Server 2012 and newer.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-28

Comments

0 comments

From Dev

Related Related

Article