Link to home
Start Free TrialLog in
Avatar of Shaunsmith
ShaunsmithFlag for United States of America

asked on

Find the Latest occurrence of a Record

I have an Employee Table wherein it records a lot of activities for each date .I have a status column (eg: W,A,S etc) that inidicates the activities .
I need to pick the latest occurance when the status column is W or A



empNo      Status      Date
1      W      2nd June 2011
1      A      3rd June 2011
2      S      1st June 2011
2      S      2nd June 2011


I would need


empNo      Status      Date

1      A      3rd June 2011
2      S      2nd June 2011
Avatar of Shaunsmith
Shaunsmith
Flag of United States of America image

ASKER

Please do note that I have reduced the complexity of teh query (multiple joins) to simply the problem so as to concentrate on what I need .

Also since we have a lot of data and joins performance is an issue as well


As always thanks for your time
SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

maybe even:

select empno,status,max(somedate)
from emp_table where status in ('W','A')
group by empno,status
:D crosspost slightwv ...