Postgres SQL to extract rows based on sequence number-CodePudding

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!

CodePudding user response：

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 ;

CodePudding user response：

You can use the RANK window function with a common table expression to get the expected results

WITH cte AS (
SELECT ID,  area,   exch,   line,   ext,    tel_type_cde, RANK() OVER (PARTITION BY id, tel_type_cde ORDER BY tel_seq_num DESC) as r
FROM telephone
)
SELECT
ID, area,   exch,   line,   ext,    tel_type_cde
FROM cte
WHERE r = 1;

Fiddle