FILTER BASE ON DATE

[sereleg]

I need some help to filter a record set using the greatest year:

My sql
select EMPLID,Action_DT from PROFILES.dbo.ps_job where Action in ('PRO','XPR') AND EMPLID in ('062497','105149')
ORDER by EMPLID des
I just place two EMPLID but could be thousands of them.

EXAMPLE

EMPLID date

105149 2005-03-28 00:00:00.000
105149 1997-02-28 00:00:00.000
105149 1998-12-23 00:00:00.000
105149 2003-04-11 00:00:00.000
105149 2000-04-04 00:00:00.000
062497 1992-05-01 00:00:00.000
062497 1998-03-04 00:00:00.000

I would like to obtain:
id date
105149 2005-03-28 00:00:00.000
062497 1998-03-04 00:00:00.000

If some one can help me I will appreciate it

Raul

 

[jdgonzalez]

Try this:

select a.emplid, a.action_dt
from profiles a
join profiles b on
    a.emplid = b.emplid and
    b.action_dt = (select max(t.action_dt)
                    from profiles t
                    where a.emplid = t.emplid)

You could also write this using a derived table rather than the corralated subquery above.

 

[jdgonzalez]

or more specifically:

select a.emplid, a.action_dt
from PROFILES.dbo.ps_job a
join PROFILES.dbo.ps_job b on
    a.emplid = b.emplid and
    b.action_dt = (select max(t.action_dt)
                    from profiles t
                    where a.emplid = t.emplid)
where a.Action in ('PRO','XPR') AND a.EMPLID in ('062497','105149')

 

[checkai]

can't you just do a simple group by? or am i being hasty...

select EMPLID,
       Max(Action_DT) as Action_DT 
from   PROFILES.dbo.ps_job 
where  Action in ('PRO','XPR') AND EMPLID in ('062497','105149')
Group
By     EMPLID

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[jdgonzalez]

In this example, you could. But if you needed to add more fields from PROFILES.dbo.ps_job, you'd get dupes on the emplid.