CODEWITHSUNDEEP
sqloracle-database
grigs
grigs

Reputation: 1150

Oracle SQL: Retrieve records by most recent date

I am attempting to pull details for records using the most recent ROLE_START_DATE. I've tried multiple methods and cant quite get it to work. It always ends up pulling all of the data, rather than specific records.

Base script: 

SELECT DISTINCT EMPLOYEE "E.EMPLOYEE #",
LR.DESCRIPTION "Role", 
R.ROLE_STATUS "Status",
R.ROLE_START_DATE "Role Start"
FROM EMPLOYEES E
JOIN ROLES R ON E.EMPLOYEE_ID = R.EMPLOYEE_ID
JOIN LU_ROLES LR ON R.ROLE_ID = LR.ROLE_ID
WHERE ROLE_START_DATE <= DATE '2017-12-03'
ORDER BY 1

Results: 

Employee #  |   Role  |  Status  |  Role Start
23432        Associate  Not Active 04/23/2011    
23432        Manager    Active     11/2/2012
54334        Analyst    Resigned   10/15/2015
12311        Help Desk  Not Active 05/12/2014
12311        Analyst    Not Active 06/11/2015
12311        Supervisor Active     07/12/2016

Modified to pull just the record with the most current date, but it doesn't work as desired, and gives me the exact same return as above. 

SELECT DISTINCT EMPLOYEE "E.EMPLOYEE #",
LR.DESCRIPTION, 
R.ROLE_STATUS,
MAX(ROLE_START_DATE)
FROM EMPLOYEES E
JOIN ROLES R ON E.EMPLOYEE_ID = R.EMPLOYEE_ID
JOIN LU_ROLES LR ON R.ROLE_ID = LR.ROLE_ID
WHERE ROLE_START_DATE >= DATE '2017-12-03'
GROUP BY E.EMPLOYEE, LR.DESCRIPTION, R.ROLE_STATUS
ORDER BY 1

What I would like to get: 

Employee #  |   Role  |  Status  |  Role Start
23432        Manager    Active     11/2/2012
54334        Analyst    Resigned   10/15/2015
12311        Supervisor Active     07/12/2016

Any help or advice would be much appreciated.

Thanks in advance!

Upvotes: 0

Views: 64

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 176264

You could use ROW_NUMBER/RANK combined with FETCH FIRST ... WITH TIES(Oracle 12c):

SELECT e.employee, lr.description, r.role_status, r.role_start_date
FROM employees e 
JOIN roles r
  ON e.employee_id = r.employee_id
JOIN lu_roles lr
  ON lr.role_id = r.role_id
 WHERE r.role_start_dt >= DATE'2017-12-03'
ORDER BY RANK() OVER (PARITION BY e.employee ORDER BY r.role_start_date DESC)
FETCH FIRST 1 ROW WITH TIES;

Upvotes: 0

David Faber
David Faber

Reputation: 12495

The best way to do this is with window functions (what Oracle terms analytic functions). If you want to return ties (employees with multiple roles with the same start date), then use RANK() or DENSE_RANK(); if you want an arbitrary role with the most recent start date, use ROW_NUMBER():

SELECT employee AS "E.EMPLOYEE #"
     , description AS "Role"
     , role_status AS "Status"
     , role_start_date "Role Start"
  FROM (
    SELECT e.employee, lr.description, r.role_status, r.role_start_date
         , RANK() OVER ( PARITION BY e.employee ORDER BY r.role_start_date DESC ) AS rn
      FROM employees e INNER JOIN roles r
        ON e.employee_id = r.employee_id
     INNER JOIN lu_roles lr
        ON lr.role_id = r.role_id
     WHERE r.role_start_dt >= DATE'2017-12-03'
) WHERE rn = 1;

Hope this helps.

Upvotes: 1

shrek
shrek

Reputation: 887

See if this helps - 

SELECT * FROM (
SELECT DISTINCT EMPLOYEE "E.EMPLOYEE #",
LR.DESCRIPTION "Role", 
R.ROLE_STATUS "Status",
R.ROLE_START_DATE "Role Start",
DENSE_RANK() OVER (PARTITION BY EMPLOYEE ORDER BY ROLE_START_DATE DESC) AS RNK
FROM EMPLOYEES E
JOIN ROLES R ON E.EMPLOYEE_ID = R.EMPLOYEE_ID
JOIN LU_ROLES LR ON R.ROLE_ID = LR.ROLE_ID
WHERE ROLE_START_DATE >= DATE '2017-12-03'
ORDER BY 1
) WHERE RNK = 1

Upvotes: 0

Related Questions