CODEWITHSUNDEEP
sqlsql-server
SamuraiJack
SamuraiJack

Reputation: 5549

SQL Query to select top 1 message from every sender

I am trying to create a private messaging system, this is what i want to do..

enter image description here

If ToId or fromid is 42 then query must return just one(most recent row) row for every toid/fromid associated with toid/from 42.

In this case it should be rows with msg ids.. 3,4,6,7

Upvotes: 1

Views: 645

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

This is an application of row_number().

select msgid, fromid, toid, msg, date
from (select t.*,
             ROW_NUMBER() over (partition by fromid, toid
                                order by msgid desc
                               ) as seqnum
      from t
      where 42 in (fromid, toid)
     ) t
where seqnum = 1;

This uses msgid to determine the most recent value. Just change the order by to order by date desc to use date.

If you want the most recent for unique pairs (regardless of order), then a bit more logic is required:

However, you need to put the messages in a "canonical" format, so (42, 43) is the same as (43, 42). The key is partitioning by the smaller value and the larger, in that order:

select msgid, fromid, toid, msg, date
from (select t.*,
             ROW_NUMBER() over (partition by (case when fromid < toid then fromid else toid end),
                                             (case when fromid < toid then toid else fromid end)
                                order by msgid desc
                               ) as seqnum
      from t
      where 42 in (fromid, toid)
     ) t
where seqnum = 1;

Upvotes: 3

Todd
Todd

Reputation: 1091

This will select top 1 message id(MsgId) from your table: 

   select MAX(MsgId) messageID
   from table 
   group by FromId, ToId
   order by messageID

Upvotes: 0

Related Questions