How to Rank Rows Within a Partition in SQL

Problem:

In the result set, you’d like to partition records and rank rows within each partition, adding another column to show rows’ ranks within the partition.

Example:

Our database has a table named magazine with data in the following columns: id (primary key), name, category, and price.

Let’s group magazines according to category and rank each group in descending order by price. Display the ranking position, name, category, and price for each magazine.

Solution:

SELECT  
  category,
  name,
  price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM magazine;

Here is the result:

Discussion:

To partition rows and rank them by their position within the partition, use the RANK() function with the PARTITION BY clause.

SQL’s RANK() function allows us to add a record’s position within the result set or within each partition. In our example, we rank rows within a partition.

The OVER() clause always comes after RANK(). OVER() must contain an ORDER BY clause. If you’re returning ranks within a partition, place a PARTITION BY clause inside the OVER() clause. PARTITION BY is followed by an expression or column name; in our example, we use the column category (PARTITION BY category).

Note: If you’re not using partitions, you can omit PARTITION BY and simply put the ORDER BY clause in OVER().

After PARTITION BY, place ORDER BY followed by the names of the sorting columns or expressions. In our example, we’re sorting in descending order by the column price (ORDER BY price DESC). The DESC keyword indicates a descending sort order.

In the above example, using RANK() with PARTITION BY divided the results set into separate groups of magazines by their category. With each group, rows were sorted by price and then ranked within that group.