jax jax - 26 days ago 9
SQL Question

Oracle - Sql query to get the latest record in the table

I have the following table and tried to run the following query to get the latest DEPTID according to the EFFDT which is
the second row (DAA System Infrastructur 1/13/2017) but I still get all of the records. What I am doing wrong here?

I did looked at similar questions but non had the same problem as I do.

select d.deptid,d.descr,d.effdt
from SYSADM.PS_DEPT_TBL d
inner join( select deptid,descr,max(to_date(effdt)) as max_date
from SYSADM.PS_DEPT_TBL
group by deptid, descr) d1
on d.deptid = d1.deptid
and to_date(effdt) = max_date
where d.deptid ='DAA'


This is the table:

DEPTID DESCR EFFDT
-------------------------------------------
DAA Telecommunications 2/18/2013
DAA System Infrastructure 1/13/2017
DAA Manager, Telecommunications 1/1/1900
DAA System Infrastructure & Contrl 7/8/2013

APC APC
Answer Source

This is your problem:

group by deptid, descr

Your subquery includes descr in the projection and grouping, so it will calculate the maximum effective date for each combination of those two columns. So it returns four rows with four different values of max_date, and that's why your final result is what it is.

The solution is simple:

select  d.deptid,d.descr,d.effdt
 from SYSADM.PS_DEPT_TBL d
 inner join( select deptid,max(to_date(effdt)) as max_date 
              from SYSADM.PS_DEPT_TBL 
              group by deptid) d1
 on d.deptid = d1.deptid  
 and to_date(effdt) = max_date
where d.deptid ='DAA'