CODEWITHSUNDEEP
sqloracle-databaseplsql
jax
jax

Reputation: 840

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

Upvotes: 0

Views: 4677

Answers (2)

SubjectDelta
SubjectDelta

Reputation: 405

Try this

SELECT * FROM (
    SELECT deptid, descr, effdt
    FROM SYSADM.PS_DEPT_TBL
    WHERE deptid = 'DAA'
    ORDER BY effdt DESC
) resultSet
WHERE ROWNUM = 1;

Upvotes: -1

APC
APC

Reputation: 146179

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'

Upvotes: 2

Related Questions