CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Chris_Finnerty
Chris_Finnerty

Reputation: 1

SQL - select most recent employee information, based on Date

I am trying to execute a simple code, which looks like what is shown below. 

SELECT MR.ID_NUMBER
, MR.LAST_NAME
, MR.FIRST_NAME
, MR.EMAIL_ADDRESS
, MR.ASSIGNMENT_STATUS
, MR.assignment_start_Date
, AD.Address_line1
, AD.Town_Or_City



FROM Master_Rv MR
, Address_Details_v AD


WHERE Last_Name = 'Test'
and AD.Person_ID = MR.PERSON_ID

When I run this code, Multiple rows of data are produced. I only want to retrieve the data with the most recent 'Assignment_Start_Date' if that is possible? I hope that makes sense.

I have done my research, but can not seem to get my head around it, hence why I have asked here. Thanks a lot in advance.

Upvotes: 0

Views: 99

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

First, learn to use proper explicit JOIN syntax. Simple rule: Never use commas in the FROM clause; always use explicit JOIN syntax.

The simplest answer to your question is to use ROW_NUMBER():

SELECT MR.ID_NUMBER, MR.LAST_NAME, MR.FIRST_NAME,
       MR.EMAIL_ADDRESS, MR.ASSIGNMENT_STATUS, MR.assignment_start_Date,
       AD.Address_line1, AD.Town_Or_City
FROM Master_Rv MR JOIN
     (SELECT ad.*,
             ROW_NUMBER() OVER (PARTITION BY Person_ID ORDER BY Assignment_Start_Date DESC) as seqnum
      FROM Address_Details_v AD
     ) AD
     ON AD.Person_ID = MR.Person_ID
WHERE MR.Last_Name = 'Test' AND seqnum = 1;

Upvotes: 1

Mottor
Mottor

Reputation: 1948

With sub select we are getting the max assignment start date for the person with the same person_id and then is used to get only this record.

SELECT MR.ID_NUMBER
, MR.LAST_NAME
, MR.FIRST_NAME
, MR.EMAIL_ADDRESS
, MR.ASSIGNMENT_STATUS
, MR.assignment_start_Date
, AD.Address_line1
, AD.Town_Or_City
FROM Master_Rv MR
, Address_Details_v AD
WHERE mr.Last_Name = 'Test'
and MR.assignment_start_Date = (select max(assignment_start_Date) from Master_Rv where person_id=mr.person_id)   
and AD.Person_ID = MR.PERSON_ID

Upvotes: 0

Rohit Kapali
Rohit Kapali

Reputation: 216

SELECT MR.ID_NUMBER
, MR.LAST_NAME
, MR.FIRST_NAME
, MR.EMAIL_ADDRESS
, MR.ASSIGNMENT_STATUS
, MR.assignment_start_Date
, AD.Address_line1
, AD.Town_Or_City



FROM Master_Rv MR
, Address_Details_v AD


WHERE Last_Name = 'Test'
and AD.Person_ID = MR.PERSON_ID
ORDER BY MR.assignment_start_Date DESC
LIMIT 10;

this will order your records to the latest assignment_start_Date and limit your rows to 10 latest data, but you should use JOIN for effective code instead of , in "FROM"

Upvotes: 0

Related Questions