Introduction
Ranking functions
Selecting n-th row
Revision

Instruction

Great. Yet another thing you can do is rank by multiple columns, each of them in the ascending or descending order of your choice. Let's pretend that a player named John has limited space on his phone, but he wants to install a relatively recent game. Take a look:

SELECT
  name,
  genre,
  editor_rating,
  RANK() OVER(ORDER BY released DESC, size ASC)
FROM game;

We first sort the rows by the release date, with the latest games coming first, and then by size, with the smallest games appearing before bigger games.

Exercise

We want to find games which were both recently released and recently updated. For each game, show name, date of release and last update date, as well as their rank: use ROW_NUMBER(), sort by release date and then by update date, both in the descending order.

Stuck? Here's a hint!

Use (ORDER BY released DESC, updated DESC) as the window definition.

Console

Code editor

Result

TableConsole