Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Window Functions in PostgreSQL

Introduction
Ranking functions
Selecting n-th row
Summary

Instruction

Good job! You may have noticed that the rank given by the function may be counter-intuitive to some extent. When we showed the rank sorted by editor_rating, the game with the lowest score got a rank of 1. We usually want it the other way around – the first place should be occupied by the best game. Luckily, this requires a very minor change to our query:

SELECT
  name,
  platform,
  editor_rating,
  RANK() OVER(ORDER BY editor_rating DESC)
FROM game;

Voila, add DESC after the column name in OVER( ... ) and the ranking order is reversed.

Exercise

Let's use DENSE_RANK() to show the latest games from our studio. For each game, show its name, genre, date of release and DENSE_RANK() in the descending order.

Stuck? Here's a hint!

Use DENSE_RANK() OVER(ORDER BY released DESC).