Question 90289 in PostgreSQL Question posted in
The official documentation can be found here.

Postgres SQL to extract rows based on sequence number – Postgresql

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!

Tags:

2

Answers


  1. 0

    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 ;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search