CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
user12990369
user12990369

Reputation:

Getting the first 5 column of a group

I have a SQL where if i run this:

SELECT  msg.message_id, u.user_id,u.color, c.comment_id FROM 
(SELECT * FROM messages WHERE receiver_id = 17 ORDER BY date DESC LIMIT 5 OFFSET 0) 
msg INNER JOIN comment c  ON c.message_id=msg.message_id 
INNER JOIN users u ON u.user_id = c.commenter_id 
ORDER BY msg.message_id, c.comment_date DESC

I get a table like this: 

message_id | user_id | color | comment_id
------------+---------+-------+------------
        149 |      31 | black |         68
        149 |      17 | gold  |         67
        152 |      17 | gold  |         71
        152 |      17 | gold  |         70
        152 |      17 | gold  |         69
        157 |      17 | gold  |         84
        157 |      17 | gold  |         83
        157 |      17 | gold  |         82
        157 |      17 | gold  |         81
        157 |      17 | gold  |         80
        157 |      17 | gold  |         79
        157 |      17 | gold  |         78
        157 |      17 | gold  |         77
        157 |      17 | gold  |         76
        157 |      17 | gold  |         75
        157 |      17 | gold  |         74
        157 |      17 | gold  |         73

I am trying to get the first 5 columns by message_id. I know that Distinct gets the only 1 column but I want 5. Is this possible to do it in one query?

My output I want is something like this

 message_id | user_id | color | comment_id
------------+---------+-------+------------
        149 |      31 | black |         68
        149 |      17 | gold  |         67
        152 |      17 | gold  |         71
        152 |      17 | gold  |         70
        152 |      17 | gold  |         69
        157 |      17 | gold  |         84
        157 |      17 | gold  |         83
        157 |      17 | gold  |         82
        157 |      17 | gold  |         81
        157 |      17 | gold  |         80

Thank you

Upvotes: 1

Views: 85

Answers (1)

GMB
GMB

Reputation: 222652

You can use window functions:

select *
from (
    select 
        m.message_id, 
        u.user_id, 
        u.color, 
        c.commenter_id,
        row_number() over(partition by message_id order by c.comment_date desc) rn
    from (select * from messages where receiver_id = 17 order by date desc limit 5) m
    inner join comments c on c.message_id = m.message_id
    inner join users u on u.user_id = c.commenter_id
) t
where rn <= 5

It should even be possible to replace the subquery that gets the top 5 messages with another window function:

select *
from (
    select 
        m.message_id, 
        u.user_id, 
        u.color, 
        c.commenter_id,
        row_number() over(partition by message_id order by c.comment_date desc) rn,
        dense_rank() over(order by m.date desc) drn
    from messages m
    inner join comments c on c.message_id = m.message_id
    inner join users u on u.user_id = c.commenter_id
    where m.receiver_id = 17 
) t
where drn <= 5 and rn <= 5

Upvotes: 1

Related Questions