SQL SubQuery Question

[tunjio]

I have got an Employee Table that looks like below:

EMPLID EFF DATE ACTION
001 05/20/2005 HIR
001 05/21/2005 TER
002 05/22/2005 HIR
002 05/22/2005 PAY
002 05/22/2005 TER
003 05/21/2005 HIR
003 05/22/2005 PAY
003 05/23/2005 PAY

The current requirement is to retrieve the latest row based on the effective date.
Thus a Sample query would be as follows:

Select * from employee
Where employee.EFFDT = (select max (K.EFFDT) from EMPLOYEE K where K.EFFDT <= ‘25-May-2005’

This would return the latest row for each employee as follows:

EMPLID EFF DATE ACTION
001 05/21/2005 TER
002 05/22/2005 TER
003 05/23/2005 PAY


I have a new requirement to retrieve the latest row but if however the latest row has an Action of ‘TER’ then bring back the row before that!

So what I would like in effect is for my query to generate the following output:

EMPLID EFF DATE ACTION
001 05/20/2005 HIR
002 05/22/2005 PAY
003 05/23/2005 PAY


Thanks

 

[oradba101]

Hi, tunijo

Try this:

Select * from employee
Where employee.EFFDT = (select max (K.EFFDT)
from EMPLOYEE K
where K.EFFDT <= ‘25-May-2005’)
AND action != 'TER'

Regards,

William Chadbourne
Oracle DBA