Appropriate SQL Server approach for retrieving this grouped data?

Question

Hi everyone,

I have a table (AU_EMPLOYEE) with two columns named EmployeeID (int) and LastModifiedDate (DateTime). Along with those columns are others containing additional employee data. This is an audit table and every time an employee's data changes in some way a new row is added.

So it's quite likely a given employee will have multiple rows in this table. I would like to retrieve the most recent record for each employee as determined by the LastModifiedDate. What is a good approach to doing this? Nested query or something along those lines?

Thanks for the suggestions.

Answer 1

Assuming at least SQL 2005 so you can use a CTE:

EDIT: As I've pointed out here and here in the past, be sure to test performance. The CTE version with MAX will often outperform a ROW_NUMBER based solution.

;with cteMaxDate as (
    select EmployeeID, max(LastModifiedDate) as MaxDate
        from AU_EMPLOYEE
        group by EmployeeID
)
select e.EmployeeID, e.Column1, e.Column2, ...
    from cteMaxDate md
        inner join AU_EMPLOYEE e
            on md.EmployeeID= e.EmployeeID
                and md.MaxDate = e.LastModifiedDate

Answer 2

You could use something like this to show the most recent row for each employee. This is a good use for the ROW_NUMBER function.

    with ranking as 
    (
        select *, ROW_NUMBER() over(partition by EmployeeID order by LastModifiedDate desc) as rn
        from AU_EMPLOYEE
    )
    select * from ranking where rn = 1

Answer 3

SELECT <your columns>
FROM (
SELECT <your columns>,
ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY LastModifiedDate DESC) AS rn
) AS t
WHERE rn=1

Answer 4

Chris Pebble's answer is correct however a more general solution is

SELECT * FROM
(SELECT EmployeeID, LastModifiedDate
FROM AU_EMPLOYEE
WHERE LastModifiedDate<='X' ORDER BY LastModifiedDate Desc) A
GROUP BY A.EmployeeID

where X is the date you want to go back in time to.