Re: Help reuired for sub-query

In article <8n8eek$iqg$1_at_nnrp1.deja.com>,   Raymond Allan <fmedit_at_my-deja.com> wrote:
> Hi,
>
> I have SQL which produces the following output:
>
> "Clock No." "Max Date" "Dept"
>
> 02753,02/01/00,531
> 02753,14/07/00,538
> 02753,18/11/99,S388888
> 03407,25/10/99,506
> 03407,03/03/00,531
> 03407,02/03/00,599
>
> SQL code:
>
> select es.employee_number "Clock No.",
> max(es.effective_date) "Max Date",
> es.assigned_department "Dept"
> from smarttime4_0.employee_schedule es
> where es.division_number=3
> and es.employee_number ='03407'
> or es.employee_number ='02753'
> group by es.employee_number,
> es.assigned_department
>
> Can anyone supply sub-query code which will only show the following:
>
> 02753,14/07/00,538
> 03407,03/03/00,531
>
> I only need to pull in the employee_number and assigned_department
 which
> has the latest effective_date.
>
> Thanks in advance for any help on this issue.
>
> --
> Rgds
> Raymond
> http://www.fmedit.pwp.blueyonder.co.uk/html/fmedit.htm
> (For the ideal Explorer replacement)
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

select es.employee_number "Clock No.",
max(es.effective_date) "Max Date",
es.assigned_department "Dept"
from smarttime4_0.employee_schedule es
where es.division_number=3
and (es.employee_number ='03407'
or es.employee_number ='02753')
and es.effective_date =

    (select max(effective_date

     from smarttime4_0.employee_schedule es1
     where es1.employee_number = es.employee_number)

Hth,

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com http://www.deja.com/
Before you buy.