Select the users I’ve talked to and the last message with them, like whatsapp

Question

I have this table in my database:

sentByand sentToare in table FK to User.

In this table, I have messages between users:

sentBy |  sentTo  |     dateSent     |     body
-------+----------+------------------+-----------------
  1    |    2     | 11/21/2010 10:00 | Hey!
-------+----------+------------------+-----------------
  2    |    1     | 11/21/2010 10:50 | Hi!
-------+----------+------------------+-----------------
  1    |    2     | 11/21/2010 10:51 | msg body 1
-------+----------+------------------+-----------------
  2    |    1     | 11/21/2010 11:05 | msg body 2
-------+----------+------------------+-----------------
  1    |    3     | 11/21/2010 11:51 | msg body 3
-------+----------+------------------+-----------------
  3    |    1     | 11/21/2010 12:05 | msg body 4
-------+----------+------------------+-----------------
  1    |    3     | 11/21/2010 12:16 | msg body 5
-------+----------+------------------+-----------------
  4    |    1     | 11/21/2010 12:25 | msg body 6
-------+----------+------------------+-----------------

I need to know the users that user 1 spoke to and the users who talked to user 1 . In this case, with users 2, 3, and 4 (note that user 4 sent a message to user 1, but user 1 has not sent a message yet).

And the second question: how can I get the last message with each user? I ask to receive the last message sent to the user.

, 1, 2 : msg body 2. 3 msg.

SQL SELECT? , , .

- WhatsApp. , ( ), ( ).

, Conversation, sentBy sentTo , , , , .

:

sentBy |  sentTo  |     dateSent     |     body
-------+----------+------------------+-----------------
  2    |    1     | 11/21/2010 11:05 | msg body 2
-------+----------+------------------+-----------------
  1    |    3     | 11/21/2010 12:16 | msg body 5
-------+----------+------------------+-----------------
  4    |    1     | 11/21/2010 12:25 | msg body 6
-------+----------+------------------+-----------------

Answer 1

1:

select m.* from messages m
join (
  select auser,withuser,max(datesent) datesent from (
    select sentby as auser,sentto as withuser,datesent from messages 
    union
    select sentto as auser,sentby as withuser,datesent from messages 
    ) as ud
  group by auser,withuser
  ) maxud 
  on (m.datesent=maxud.datesent and maxud.auser in (m.sentBy,m.sentTo))
where auser=1

, where, .

, , :

create view conversation_stuff as
select m.sentBy,m.sentTo,m.dateSent,m.body,maxud.auser,maxud.withuser
from messages m
join (
  select auser,withuser,max(datesent) datesent from (
    select sentby as auser,sentto as withuser,datesent from messages 
    union
    select sentto as auser,sentby as withuser,datesent from messages 
    ) as ud
  group by auser,withuser
  ) maxud 
  on (m.datesent=maxud.datesent and maxud.auser in (m.sentBy,m.sentTo))

select sentBy,sentTo,dateSent,body from conversation_stuff where auser=1;

, .

EDIT: user auser , sqlserver [] s...

Answer 2

dateSent, sentBy sentTo, .

. . , ?

SELECT
    Messages.*
FROM
    Messages
    INNER JOIN
    (
        SELECT
            CombinedKey,
            MAX(dateSent) AS dateSent
        FROM
        (
            SELECT
                CombinedKey = CASE WHEN sentBy > sentTo
                    THEN CAST(sentBy AS nvarchar(10)) + '_' 
                        + CAST(sentTo AS nvarchar(10))
                    ELSE CAST(sentTo AS nvarchar(10)) + '_'
                        + CAST(sentBy AS nvarchar(10))
                    END,
                MAX(dateSent) AS dateSent
            FROM
                Messages
            WHERE
                sentBy = @user
                OR sentTo = @user
            GROUP BY
                sentBy,
                sentTo
        ) AS MaxMessagesBoth
        GROUP BY
            CombinedKey
    ) AS MaxMessages    
        ON MaxMessages.CombinedKey = CASE WHEN sentBy > sentTo
            THEN CAST(sentBy AS nvarchar(10)) + '_'
                + CAST(sentTo AS nvarchar(10))
            ELSE CAST(sentTo AS nvarchar(10)) + '_'
                + CAST(sentBy AS nvarchar(10))
            END
        AND MaxMessages.dateSent = Messages.dateSent