0

Below is my Table Structure with data:

ID Date EmpId 67 2015-08-24 10:44:33.087 293 68 2015-08-24 10:41:49.950 293 69 2015-08-24 10:42:49.951 293 70 2015-08-24 10:45:15.157 013 71 2015-08-24 10:46:33.131 013 72 2015-08-24 10:42:33.134 013 73 2015-08-24 10:47:33.136 013

ID Column is a auto generated column. For every employee there are 2 or more than 2 records. In this case. EmpId - 293 has got three records. 013 has got 4 records. I am looking for a query to return record with maximum Date for every employee. In this case the result should be as below:

ID Date EmpId 69 2015-08-24 10:44:33.087 293 73 2015-08-24 10:47:33.136 013

My requirement is to return all 3 columns in the result set.

Improve this question

4 Answers 4

Reset to default
3 
select ID,Date,EMPID from ( select ID, Date, EMPId, ROW_NUMBER() over (partition by EMPId order by Date desc) as RowNUm from Employee ) A where RowNUm = 1
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Try this

WITH TempId AS ( SELECT *, row_number() OVER(PARTITION BY EmpID ORDER BY ID desc) AS [Num] FROM Employee1 ) DELETE TempId WHERE [Num] > 1 --For deleting record Select * from Employee1 --For Selecting record

Find solution in Fiddle http://sqlfiddle.com/#!6/80f3d/3

Improve this answer

Comments

0 
DELETE FROM (select ID ,Date , EMPId , ROW_NUMBER() over (partition by EMPId order by Date desc) as RowNum from Employee) WHERE RowNum > 1

Above query will remove any duplicate empid (the older one).

Improve this answer

Comments

0

This query worked for me:

SELECT t.ID, t._Date, t.EmpId FROM test t LEFT JOIN test t_theta ON t.EmpId = t_theta.EmpId AND t._Date < t_theta._Date WHERE t_theta.ID IS NULL

Basically, you use a theta join to join the table to itself. Then, you limit it to return records in the first table where there are no greater dates for that employee ID in the second table. Enjoy!

Fiddle : http://sqlfiddle.com/#!6/dd2b1/1

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.