Introduction
Ranking functions
Selecting n-th row
Summary

Instruction

Great! Another thing you can do is rank by multiple columns, with each rank of them in ascending or descending order as you chose. Let's pretend that a player named John has limited space on his phone, but he wants to install one of our newer games. How can we rank his options by both release date and game size? Take a look:

SELECT
  Name,
  Genre,
  EditorRating,
  RANK() OVER(ORDER BY ReleaseDate DESC, Size ASC)
FROM Game;

We first sort the rows by the release date, with the newest games coming first (DESC). Then we rank by size, with the smaller games appearing first (ASC).

Exercise

We want to find games which were both recently released and recently updated. For each game, show its name, its date of release, and the date of its most recent update. Rank them based first on the release date and then on the update date, both in descending order. Use ROW_NUMBER() and name the column RowNumber.

Stuck? Here's a hint!

Use (ORDER BY ReleaseDate DESC, UpdateDate DESC) as the window definition.