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.
CodePudding user response：
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