Here's an example:
Name New Job EffDt Old Job Effdt Joe Smith Analyst III 07/23/2013 Analyst I 01/19/2010 Joe Smith Analyst III 07/23/2013 Analyst II 05/20/2011 Joe Smith Analyst III 07/23/2013 Sales Re
Name would be the common field in both tables to join on. I only need the max effective date for the old job table.
Any suggestions? Thanks.
Can anyone help?
You've tagged this Oracle which has the DENSE RANK keyword(s). This allows you to create an in-line select which can pick the value of one field based on the max value of another.
SELECT staff_name, MAX (job_title) KEEP (DENSE_RANK FIRST ORDER BY EffDt ASC) job_title
GROUP BY staff_name
Look-up "Oracle DENSE RANK" in web search engine
Thank you. Testing this looks like it should do what I need it to do.