Avatar of dyarosh

asked on 

Retrieving the first record for each employee in a table using Oracle SQL

I have a two tables. One table has employees in it with employeeid as the unique key.  The other table is an employee history table with employeehistoryid as the unique key.  The employee history table has multiple records for each employee.  I need to retrieve the most recent employee history record for each employee where the statusid in the employee history table is not 30 or 31.

Here is what I have so far:
WITH emp AS ( 
  SELECT * FROM emp_owner.emp_employee 
  WHERE ( statusid IN (1,2,3,7,8,9,14) OR ( statusid IN (30,31) AND terminationdate + interval '1' day > sysdate ) ) AND LastName IS NOT NULL AND LocationID NOT IN (0,99) 
  ORDER BY LastName, FirstName ),
h AS (
  SELECT EmployeeID, StatusID FROM emp_employeehistory
  WHERE StatusID NOT IN (30, 31)
  ORDER BY EmployeeID, EmployeeHistoryID DESC)
SELECT EmployeeId, 
 FROM h WHERE RowNum = 1) AS CurrentStatus,
 FirstName, LastName
FROM emp;

Open in new window

The problem is all of the CurrentStatus records are coming back with 1 which is not correct.

Here is an example of what the data could look like:
Employee Table

EmployeeID     LastName     FirstName
1                        Doe                John
2                        Smith             Jane
3                        Jones             Tom

Employee History Table

EmployeeHistoryID     EmployeeID     StatusID
1                                     1                        1
2                                     2                        1
3                                     1                        2
4                                     3                        1
5                                     1                        30
6                                     2                        31
7                                     3                        1

Open in new window

Given the data above I am looking for the following results:
EmployeeID     StatusID
1                         2
3                         1
2                         1

Open in new window

Any help is greatly appreciated!
Oracle Database

Avatar of undefined
Last Comment
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

your sample data is missing a few columns to replicate the query

locationid, terminationdate, statusid

I faked it with the following values for each emp_employee record

locationid = -1
terminationdate = sysdate+7
statusid = 1

and then correlating the subquery by employeeid, this returned the data you were looking for

SELECT employeeid,
       (SELECT statusid
          FROM h
         WHERE h.employeeid = emp.employeeid AND ROWNUM = 1)
           AS currentstatus,
  FROM emp;

Open in new window

Avatar of dyarosh


Thank you.  That gave me exactly what I needed.
Oracle Database
Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo