Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query - Most recent entry per user

Author  Topic 

Phantek
Starting Member

3 Posts

Posted - 2013-07-16 : 18:13:17
Hello everyone,

I understand the basics of SQL, but this one is certainly beyond me.

There is a large data table called emp_history on an SQL Server, which contains the employment history of each employee. The significant columns are as follows:

entry_id employee_id start_date position

where entry_id is the primary key and auto-increments.

What I need, is to create a query to extract the employee_id and position, based on the most recent record (most recent start_date) for each employee.

Any idea how I could go about this?

Thank you in advance for your help!

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-16 : 20:20:59
Try something like this:
[CODE]

; WITH CTE AS
(SELECT *, ROW_NUMBER() OVER(PARTITION BY employee_id order by startdate DESC) as RN
from emp_history)
SELECT employee_id, position from CTE WHERE RN = 1;

[/CODE]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 01:12:14
[code]
SELECT entry_id, employee_id, start_date, position
FROM
(
SELECT *,
MAX(start_date) OVER(PARTITION BY employee_id) as latestdate
from emp_history
)t
WHERE start_date = latestdate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Phantek
Starting Member

3 Posts

Posted - 2013-07-17 : 08:22:02
Both options work perfectly, and quickly - thank you both for taking the time to help me with that!

If I could possibly go one step further, and ask how I could join that to another query, in which I retrieve the employee name. There is another table (employee_name), which contains the employee_id, first_name, and last_name fields. I would like to be able to retrieve the employee's first and last names, along with their current position.

Thanks again!
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-17 : 08:57:54
[CODE]

; WITH CTE AS
(SELECT *, ROW_NUMBER() OVER(PARTITION BY employee_id order by startdate DESC) as RN
from emp_history)
SELECT T1.employee_id, T1.position, T2.first_name, T2.last_name
from employee_name T1 INNER JOIN CTE T2
ON T1.employee_id = T2.employee_id
WHERE RN = 1;

[/CODE]
Go to Top of Page

Phantek
Starting Member

3 Posts

Posted - 2013-07-17 : 09:44:20
Again, worked perfectly. Thank you very much!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 11:01:25
[code]
SELECT t.entry_id, t.employee_id, t.start_date, t.position
FROM
(
SELECT *,
MAX(start_date) OVER(PARTITION BY employee_id) as latestdate
from emp_history
)t
INNER JOIN employee_name en
ON en.employee_id = t.employee_id
WHERE t.start_date = t.latestdate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -