CODEWITHSUNDEEP
sql
user856358
user856358

Reputation: 593

SQL: filter a joined table

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.

Upvotes: 2

Views: 221

Answers (4)

Uday
Uday

Reputation: 21

Try This one. It should work :

select user_ID,
text,
u.name,
max(date) from user u inner join messages m on m.user_id=u.id group by user_ID,
text,
u.name

Upvotes: 0

Egor Lyah
Egor Lyah

Reputation: 59

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.

This code work in SQL Server 2012 and newer.

Upvotes: 2

Racheli
Racheli

Reputation: 111

You can join the tables and than filter the results:

select tbl.name , tbl.Text from
(select User.name,
        Messages.Text,
        RANK() OVER (PARTITION BY User.name ORDER BY Messages.date desc) AS  rank  
from User inner join Messages 
on User.id = Messages.user_id) as tbl
where rank=1

Upvotes: 2

Kiri
Kiri

Reputation: 26

Something to get you started:

select u.id, u.name, m.text
from User u
inner join Messages m on m.user_id = u.id
inner join (
    select m.user_id, MAX(date) as max_date
    from User u
    inner join Messages m on m.user_id = u.id
    group by m.user_id
) t on t.user_id = m.user_id and m.date = t.max_date

Upvotes: 0

Related Questions