I have data in Telephone table, something as below:
| ID | area | exch | line | ext | tel_type_cde | tel_seq_num | modified_dttm |
|---|---|---|---|---|---|---|---|
| 1234 | 482 | 876 | 789 | 1234 | 0 | 0 | 01-01-2023 |
| 1234 | 483 | 877 | 123 | 0 | 1 | 01-02-2023 | |
| 1234 | 123 | 234 | 456 | 1234 | 1 | 0 | 01-01-2023 |
| 1235 | 483 | 877 | 456 | 0 | 1 | 01-01-2023 | |
| 1236 | 483 | 877 | 123 | 0 | 0 | 01-02-2023 | |
| 1236 | 123 | 234 | 456 | 1234 | 0 | 1 | 01-02-2023 |
| 1236 | 483 | 877 | 458 | 0 | 2 | 01-03-2023 |
For an ID/tel_type_cde combination there might be multiple rows and telephone sequence number is used to derive from latest number which needs to considered further
Expected output
| ID | area | exch | line | ext | tel_type_cde |
|---|---|---|---|---|---|
| 1234 | 483 | 877 | 123 | 0 | |
| 1234 | 123 | 234 | 456 | 1234 | 1 |
| 1235 | 483 | 877 | 456 | 0 | |
| 1236 | 483 | 877 | 458 | 0 |
Drafted below query so far, but not working as expected
select distinct on (ID)
ID,
area,
exch,
line,
ext,
tel_type_cde
from telephone
order by ID,tel_seq_num desc;
Appreciate any help!
>Solution :
try this :
SELECT DISTINCT ON (id, tel_type_cde)
id, area, exch, line, ext, tel_type_cde
FROM telephone
ORDER BY id, tel_type_cde, tel_seq_num DESC ;