CODEWITHSUNDEEP
sql-serverselectgroup-by
larryq
larryq

Reputation: 16309

Appropriate SQL Server approach for retrieving this grouped data?

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.

Upvotes: 2

Views: 203

Answers (4)

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

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

Upvotes: 3

matthewaveryusa
matthewaveryusa

Reputation: 652

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.

Upvotes: 2

A-K
A-K

Reputation: 17090

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

Upvotes: 3

Mike Forman
Mike Forman

Reputation: 4537

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

Upvotes: 6

Related Questions