Appropriate SQL Server approach for retrieving this grouped data?

Question

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

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