SQL Query Help - Getting Results from a Single Table of latest unique record per "pairing"

Question

I have a mssql table with data like the following:

ID	Type	ToNum	FromNum	sDate	Msg
1	1	6025551212	6235551234	2022-11-05 13:44:00	This is a test
2	1	4805554321	6235551234	2022-11-05 13:50:00	Test message
3	2	6235551234	4805554321	2022-11-05 13:51:00	Response to your message
4	1	4805554321	6235551234	2022-11-05 14:01:00	Got your response!
5	1	4805557891	6235551234	2022-11-05 14:02:00	Test to -7891
6	2	6235551234	4805557891	2022-11-05 14:04:00	Hi! Thanks.

What I want is a SQL query that will get me a Result Set that contains rows of data will all columns, but only the latest of any ToNum / FromNum pairing. The Result Set would look like this:

ID	Type	ToNum	FromNum	sDate	Msg
1	1	6025551212	6235551234	2022-11-05 13:44:00	This is a test
4	1	4805554321	6235551234	2022-11-05 14:01:00	Got your response!
6	2	6235551234	4805557891	2022-11-05 14:04:00	Hi! Thanks.

I've tried various methods of JOINS, UNIONS, etc but can't figure out how to get what I'm looking for.

Answer 1

We can use row_number() for this ; the trick is to partition correctly:

select t.*
from (
    select t.*,
        row_number() over(
            partition by least(tonum, fromnum), greatest(tonum, fromnum) 
            order by sdate desc
        ) rn
    from mytable t
) t
where rn = 1

least() and greatest are available in SQL Server 2022 only. In earlier versions, we can fallback on case expressions:

select t.*
from (
    select t.*,
        row_number() over(
            partition by 
                case when tonum < fromnum then tonum else fromnum end,
                case when tonum > fromnum then tonum else fromnum end
            order by sdate desc
        ) rn
    from mytable t
) t
where rn = 1