Avatar of Shaunsmith
Shaunsmith
Flag 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
Oracle DatabaseSQL

Avatar of undefined
Last Comment
Gerwin Jansen

8/22/2022 - Mon
Shaunsmith

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
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

maybe even:

select empno,status,max(somedate)
from emp_table where status in ('W','A')
group by empno,status
Gerwin Jansen

:D crosspost slightwv ...
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes