Hi Team
Please find re-producible data available in LiveSQL shared link ->
https://livesql.oracle.com/ords/livesql/s/c9r56fcrqizrcrc5aiep4hrpn Requirement is to fetch last record for "t_id" column value for each "code" + "status" column values respectively.
Looking for options on how to fetch below data w/ SQL.
ID T_ID CODE STATUS
---------- ---------- ------------- ------------
<id_val> t1 Lookup Enable -- fetch last record
<id_val> t1 Lookup Disable -- fetch last record
<id_val> t1 Sync Enable -- fetch last record
<id_val> t1 Sync Disable -- fetch last record
<id_val> t2 Lookup Enable -- fetch last record
<id_val> t2 Lookup Disable -- fetch last record
<id_val> t2 Sync Enable -- fetch last record
<id_val> t2 Sync Disable -- fetch last record
<id_val> t2 Search Enable -- fetch last record
<id_val> t2 Search Disable -- fetch last record
Appreciate all the help provided in Asktom.
Thanks..
Use a subquuery to number the rows in the desired order. Then filter the result of this calculation to the rows with number 1, e.g.:
with numbered_rows as (
select t.*,
row_number () over (
partition by t_id, code, status
order by upd_date desc
) rn
from test t
)
select t_id, code, status, upd_date
from numbered_rows
where rn = 1;
T_ID CODE STATUS UPD_DATE
t1 Disc Disable 01/04/2025 12:40:00.000000000
t1 Lookup Disable 01/04/2025 12:40:00.000000000
t1 Lookup Enable 12/03/2025 12:40:00.000000000
t1 Sync Disable 01/04/2025 12:40:00.000000000
t1 Sync Enable 12/03/2025 12:40:00.000000000
t2 Disc Enable 01/04/2025 12:40:00.000000000
t2 Lookup Disable 01/04/2025 12:40:00.000000000
t2 Lookup Enable 12/03/2025 12:40:00.000000000
t2 Search Disable 01/04/2025 12:40:00.000000000
t2 Search Enable 12/03/2025 12:40:00.000000000
t2 Sync Disable 12/03/2025 12:40:00.000000000
t2 Sync Enable 02/03/2025 12:40:00.000000000 From Oracle Database 23ai, you can do this with the partition by clause of fetch first:
select t_id, code, status, upd_date
from test
order by t_id, code, status, upd_date desc
fetch first
999999999 partition by t_id,
999999999 partition by code,
999999999 partition by status,
1 row only;
T_ID CODE STATUS UPD_DATE
t1 Disc Disable 01/04/2025 12:40:00.000000000
t1 Lookup Disable 01/04/2025 12:40:00.000000000
t1 Lookup Enable 12/03/2025 12:40:00.000000000
t1 Sync Disable 01/04/2025 12:40:00.000000000
t1 Sync Enable 12/03/2025 12:40:00.000000000
t2 Disc Enable 01/04/2025 12:40:00.000000000
t2 Lookup Disable 01/04/2025 12:40:00.000000000
t2 Lookup Enable 12/03/2025 12:40:00.000000000
t2 Search Disable 01/04/2025 12:40:00.000000000
t2 Search Enable 12/03/2025 12:40:00.000000000
t2 Sync Disable 12/03/2025 12:40:00.000000000
t2 Sync Enable 02/03/2025 12:40:00.000000000