Postgres SQL to extract rows based on sequence number

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 ;

Leave a Reply