Rank function in MySQL

sql

rank-function

mysql-optimization

sql-queries

byNikita Barsukov·Jan 4, 2025

Make use of MySql's RANK() function to assign sequential rankings to rows in your data. Tied values are given the same rank, with the subsequent ranks skipping numbers accordingly. Here's a simple example to illustrate how to implement this:

SELECT
  data_field,
  RANK() OVER (ORDER BY data_field DESC) AS rank_col
FROM
  table_name;

This query ranks rows according to data_field, treating duplicates as identical, and orders them in descending order.

Manual ranking with variables in MySQL

Before MySQL 8.0, you had to resort to alternative methods as window functions were not supported. Using user-defined variables to mimic RANK() has been one of the common workarounds.

User variables for rank emulation

For cases that need partitioning, such as ranking customers by gender and age, you can use variables to emulate partitioned ranking:

SET @prev_value = NULL;
SET @rank_count = 0;

SELECT
  first_name,
  age,
  gender,
  CASE
    WHEN @prev_value = gender THEN @rank_count
    WHEN @prev_value := gender THEN @rank_count := @rank_count + 1  # Just like counting sheep, but with genders
  END AS rank
FROM
  (SELECT first_name, age, gender
   FROM customers
   ORDER BY gender, age DESC) AS sorted_data;

Rank calculation using self-joins

Self-joins can come in handy when you are calculating ranks. It's like when you high-five yourself—weird, yes, but fully functional:

SELECT
  a.first_name,
  a.age,
  a.gender,
  (SELECT COUNT(*) FROM customers b 
   WHERE b.gender = a.gender AND b.age >= a.age) AS rank  # It's a small world after all!
FROM
  customers a;

Breaking down complexity with subqueries

Subqueries are the Swiss army knives of SQL. They can simplify complex problems and make your code cleaner, just like taking a hot shower:

SELECT
  first_name,
  age,
  gender,
  (SELECT COUNT(DISTINCT age) FROM customers c 
   WHERE c.gender = customers.gender AND c.age > customers.age) + 1 AS rank  # "+1", because everybody deserves to be a rank higher
FROM
  customers;

Coping with tied ranks using IF()

Tied ranks can be a headache, but MySQL's IF() function is the aspirin you need. It checks if consecutive rows are equal and, if so, allots them the same rank:

SELECT
  first_name,
  age,
  gender,
  @curRank := IF(@prevVal = age, @curRank, @curRank + 1) AS rank,
  @prevVal := age
FROM
  customers,
  (SELECT @curRank := 0, @prevVal := NULL) r
ORDER BY
  age DESC;  # descending, because we are rebels

Diving deeper — advanced ranking techniques

Variable assignment and non-null values

Data consistency matters. Ensure accurate variable assignment with IS NOT NULL, preventing any potential null value conundrums:

SELECT
  IF(@prevAge IS NOT NULL AND @prevAge = age, @curRank, @curRank := @curRank + 1) AS rank,
  @prevAge := age AS dummy  # Not a real dummy, just a SQL dummy
FROM
  customers,
  (SELECT @curRank := 0) r
ORDER BY
  age DESC;  # Because we still like doing things in reverse

ANSI to MySQL — The SQL dialect shifts

RANK() functions in most ANSI-compliant SQL languages may not behave the same way in MySQL. Consequently, you'll need to convert them using a combination of variables and sorting logic.

MySQL's unique ranking behavior

While MySQL lets you replicate the RANK() functionality using smart techniques, remember that it lacks the fully-fledged windowing capabilities of ANSI SQL RANK() function. However, these methods will suffice for most practical requirements.

Optimization — make your rank queries run faster

In optimizing your queries, you should group and sort your data appropriately to ensure that the rank computation operates at peak efficiency:

SELECT
  first_name,
  age,
  gender,
  @curRank := IF(@prevGender = gender, IF(@prevAge = age, @curRank, @curRank + 1), 1) AS rank,
  @prevAge := age,
  @prevGender := gender
FROM
  customers,
  (SELECT @curRank := 0, @prevAge := NULL, @prevGender := NULL) r
ORDER BY
  gender, age DESC;  # Gender first, because ladies first.

Multiple paths — choosing the right MySQL ranking method

Understanding differences between MySQL's ranking method and the ANSI standard will not only improve the quality of your queries but also increase the portability of your SQL skills across different database systems.

Hands-on practice with dbfiddle

dbfiddle.uk, an online platform, allows you to test and comprehend these ranking implementations in a risk-free environment. It's like an SQL sandbox, have fun!

explain-codes / Sql / Rank function in MySQL

Linked

The quandary of the GROUP BY clause and aggregate functions in SQL



What is ROWS UNBOUNDED PRECEDING used for in Teradata?



Select the max value of each group



Row_number() in MySQL



Select a column in SQL not in Group By

