0

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?

MT0
  • 143,790
  • 11
  • 59
  • 117
user11742091
  • 13
  • 4
  • I don't understand the logic behind your expected output. – Tim Biegeleisen Aug 26 '22 at 01:21
  • @TimBiegeleisen it's like a workflow. I wan't to select the latest records with unique recevier. Because record id 2 & 5 same receiver B, so i want to select the latest is record id 5. Similar record id 4 & 6. Receiver A & C has only one, so selected them – user11742091 Aug 26 '22 at 01:29

1 Answers1

0

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360