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.
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 positionwhere 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, positionFROM(SELECT *, MAX(start_date) OVER(PARTITION BY employee_id) as latestdate from emp_history)tWHERE start_date = latestdate[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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.positionFROM(SELECT *, MAX(start_date) OVER(PARTITION BY employee_id) as latestdate from emp_history)tINNER JOIN employee_name enON en.employee_id = t.employee_idWHERE t.start_date = t.latestdate[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|