I have a table save sending & receiving process as example
| id | sender | receiver |
|---|---|---|
| 1 | A | A |
| 2 | A | B |
| 3 | A | C |
| 4 | B | D |
| 5 | D | B |
| 6 | B | D |
But i don’t know how to write oracle query to select the latest with unique receiver. Expected result as:
| id | sender | receiver |
|---|---|---|
| 1 | A | A |
| 3 | A | C |
| 5 | D | B |
| 6 | B | D |
Any ideas on how I can accomplish the result i’m after?
>Solution :
We can use ROW_NUMBER() here:
WITH cte AS (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY receiver ORDER BY id DESC) rn
FROM yourTable t
)
SELECT id, sender, receiver
FROM cte
WHERE rn = 1
ORDER BY id;