Self Join Query help needed -- extract last row of data only

[HRISmess]

I have some queries written in Oracle SQL that I need to jam into an Access Design grid - long story - and need some help.

Essentially, I have a table with Employee personnel transactions and need a query that can identify the last of those transactions on any given day. So, I know I need a self-join on the fields themselves, but can't nail down the syntax to apply the MAX function properly.

Here is Table - named J:

EmployeeID
PersonnelAction
PersonnelActionDate
ActionSequence(field that I need to apply MAX to)

Can anyone help with what the SQL should look like? Or, the design grid?

Cheers!

 

[lespaul]

Something like:

SELECT J.EmployeeID, PersonnelAction, PersonnelActionDate, MaxActionSequence
FROM J
INNER JOIN (SELECT EmployeeID, Max(ActionSequence) FROM J GROUP BY EmployeeID) B On J.EmployeeID = B.EmployeeID

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

Leslie, I'd use this join criteria:
ON J.EmployeeID = B.EmployeeID AND J.ActionSequence = B.MaxActionSequence

 

[lespaul]

yep you're right, must have been too early in the morning (plus had a REALLY bad night and it's carried over to today as well)

les

 

[HRISmess]

You guys are the best. As always, thank you for making my work life a little brighter!

 

[HRISmess]

One more question on the topic:

If I had to target a particular PersonnelAction that had Multiple Rows of data, how would the query change?

For instance:

EMPLOYEEID P_Action P_ActionDt EFFSEQ
123456789 TER 12/31/2006 0
123456789 TER 12/31/2006 1
123456789 MPR 1/1/2007 1
123456789 MPR 1/1/2007 0
123456789 TER 12/31/2006 2
123456789 CPR 2/1/2007 0
123456789 CPR 2/1/2007 3

If I wanted to isolate the 02/01/2007 transaction, how can I tweak the SQL to capture the record with the latest EFFSEQ - WHERE P_Action = "CPR" ?

 

[lespaul]

maybe:

SELECT J.EmployeeID, PersonnelAction, PersonnelActionDate, MaxActionSequence
FROM J
INNER JOIN (SELECT EmployeeID, Max(ActionSequence) As MaxActionSequence FROM J WHERE P_Action = "CPR" GROUP BY EmployeeID) B On J.EmployeeID = B.EmployeeID AND J.ActionSequence = B.MaxActionSequence