SQL Query - Most recent entry per user

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]

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

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!

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]

Phantek
Starting Member

3 Posts

Posted - 2013-07-17 : 09:44:20

Again, worked perfectly. Thank you very much!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources