Microsoft SQL Server OVER Clause Using Aggregation funtions to find the most recent records
Example
Using the Library Database, we try to find the last book added to the database for each author. For this simple example we assume an always incrementing Id for each record added.
SELECT MostRecentBook.Name, MostRecentBook.Title
FROM ( SELECT Authors.Name,
Books.Title,
RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.Id DESC) AS NewestRank
FROM Authors
JOIN Books ON Books.AuthorId = Authors.Id
) MostRecentBook
WHERE MostRecentBook.NewestRank = 1
Instead of RANK, two other functions can be used to order. In the previous example the result will be the same, but they give different results when the ordering gives multiple rows for each rank.
RANK(): duplicates get the same rank, the next rank takes the number of duplicates in the previous rank into accountDENSE_RANK(): duplicates get the same rank, the next rank is always one higher than the previousROW_NUMBER(): will give each row a unique 'rank', 'ranking' the duplicates randomly
For example, if the table had a non-unique column CreationDate and the ordering was done based on that, the following query:
SELECT Authors.Name,
Books.Title,
Books.CreationDate,
RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS RANK,
DENSE_RANK() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS DENSE_RANK,
ROW_NUMBER() OVER (PARTITION BY Authors.Id ORDER BY Books.CreationDate DESC) AS ROW_NUMBER,
FROM Authors
JOIN Books ON Books.AuthorId = Authors.Id
Could result in:
| Author | Title | CreationDate | RANK | DENSE_RANK | ROW_NUMBER |
|---|---|---|---|---|---|
| Author 1 | Book 1 | 22/07/2016 | 1 | 1 | 1 |
| Author 1 | Book 2 | 22/07/2016 | 1 | 1 | 2 |
| Author 1 | Book 3 | 21/07/2016 | 3 | 2 | 3 |
| Author 1 | Book 4 | 21/07/2016 | 3 | 2 | 4 |
| Author 1 | Book 5 | 21/07/2016 | 3 | 2 | 5 |
| Author 1 | Book 6 | 04/07/2016 | 6 | 3 | 6 |
| Author 2 | Book 7 | 04/07/2016 | 1 | 1 | 1 |
