How can I get the lastest Date for each record?

Question

I have this table in SQL Server:

DepartmentID	Department	EmployeeID	Rate	RateModifiedDate
16	Executive	234	39.0600	2009-01-31 00:00:00.000
16	Executive	234	48.5577	2011-11-14 00:00:00.000
16	Executive	234	60.0962	2012-01-29 00:00:00.000
16	Executive	1	125.5000	2009-01-14 00:00:00.000

I want the latest RateModifiedDate for each employee like this:

DepartmentID	Department	EmployeeID	Rate	RateModifiedDate
16	Executive	234	60.0962	2012-01-29 00:00:00.000
16	Executive	1	125.5000	2009-01-14 00:00:00.000

I tried `SELECT` DeparmentID, Department, EmployeeID, Rate, RateModifiedDate `FROM` t.[Table] `WHERE` [Table].RateModifiedDate = `MAX`([Table].RateModifiedDate) — Edwin Iraheta, Nov 06 '22 at 06:31

Jonas Metzler · Accepted Answer · 2022-11-06T07:51:29.637

You are on the right track to use MAX to find the latest date. The reason why your query doesn't work is that you need to find more than one latest data. You require the latest date per employee. This can be done using a subquery:

SELECT DepartmentID, Department, EmployeeID, rate, RateModifiedDate
  FROM yourtable y1
 WHERE RateModifiedDate =
       (SELECT MAX(y2.RateModifiedDate)
          FROM yourtable y2
         WHERE y1.EmployeeID = y2.EmployeeID)
 ORDER BY RateModifiedDate DESC;

The main query will select all columns that should be shown and sort them by the date, beginning with the latest one. The sub query will find the latest date per employee. The WHERE clause of the main query will make sure that only those entries will be selected whose date matches the latest date of the current employee. Thus, the outcome will be exactly as requested.

This query will be executed on each DB type since it doesn't contain DB type-specific syntax.

Beside this general option, common DB's provide window functions like RANK that make such things "on their own". So you could also create such a query:

SELECT DepartmentID, Department, EmployeeID, rate, RateModifiedDate
  FROM (SELECT DepartmentID,
               Department,
               EmployeeID,
               rate,
               RateModifiedDate,
               RANK() OVER(PARTITION BY EmployeeID ORDER BY RateModifiedDate DESC) dest_rank
          FROM yourtable) sub
 WHERE dest_rank = 1
 ORDER BY RateModifiedDate DESC;

The outcome of this query will be the same.

The PARTITION BY clause in the sub query will group the data by the employee, the ORDER BY clause will sort it by the date, beginning with the latest. The WHERE clause of the main query will take only the data having the latest date of the sorted list from the subquery.

This query will not be executed on each DB type because the syntax and naming of window functions often differ, also older DB versions might not provide them.

This will be the result of both queries above:

DepartmentID	Department	EmployeeID	rate	RateModifiedDate
16	Executive	234	60.0962	2012-01-29
16	Executive	1	125.5000	2009-01-14

Try out here: db<>fiddle

How can I get the lastest Date for each record?

1 Answers1