MySQL SQL SQL Server

Reproducing ROW_NUMBER() in MySQL

By on Monday, March 12, 2018

Recently I was working on a project involving MySQL and needed to partition and rank results in a SELECT statement. This is a pretty straightforward approach in SQL Server using ROW_NUMBER() or RANK(). In my particular case I needed functionality equivalent to ROW_NUMBER() with partitioning by a single column and sorting by another. Below is an alternative since earlier versions of MySQL do not have an equivalent ROW_NUMBER() function.

Data

I am interested in ranking the highest sale days for each company based on the table below.

company_id company_name sale_date total_amount
1 Company A 2018-03-01 500.00
2 Company B 2017-09-15 700.00
1 Company A 2017-07-02 900.00
2 Company B 2017-06-10 560.00
2 Company B 2018-03-03 1000.00
1 Company A 2018-03-03 100.00
2 Company B 2017-12-01 650.00

*company_sales table

SQL Server query

Using T-SQL for SQL Server my query would be this:

SELECT
    company_name,
    sale_date,
    total_amount,
    ROW_NUMBER() OVER(PARTITION BY company_id ORDER BY total_amount DESC) AS day_rank
FROM
    company_sales

and the results:

company_name sale_date total_amount day_rank
Company A 2017-07-02 900.00 1
Company A 2018-03-01 500.00 2
Company A 2018-03-03 100.00 3
Company B 2018-03-03 1000.00 1
Company B 2017-09-15 700.00 2
Company B 2017-12-01 650.00 3
Company B 2017-06-10 560.00 4

MySQL query

In order to perform the same query in MySQL, I need to do things a little differently as MySQL does not have directly equivalent functions to ROW_NUMBER() and RANK().

Conveniently, one feature that MySQL does have is the ability to assign and read variables inside a SELECT statement. Using this, I can write an equivalent statement in MySQL.

SET @rank = 0;
SELECT
    company_name,
    sale_date,
    total_amount,
    day_rank
FROM (
    SELECT
        company_name,
        sale_date,
        total_amount,
        @rank := IF( @comp_id = company_id, @rank + 1, 1) AS day_rank,
        @comp_id := company_id
    FROM
        company_sales
    ORDER BY company_id, total_amount DESC
) rankings
ORDER BY company_name, day_rank

MySQL discussion

The query for MySQL is noticeably longer and a little more complicated, but it gives us the exact same output. Let’s break it down a bit and step through each piece.

1. Initialize a ranking variable:

SET @rank = 0;

2. Write the sub query

SELECT
    company_name,
    sale_date,
    total_amount,
    @rank := IF( @comp_id = company_id, @rank + 1, 1) AS day_rank,
    @comp_id := company_id
FROM
    company_sales
ORDER BY company_id, total_amount DESC

There are 3 important pieces to this sub SELECT

  • Decide the ranking logic. This is equivalent to PARTION BY...ORDER BY in SQL Server. For this query, I want to partition by company and rank by total_amount, largest to smallest. To accomplish this, I double sort, first by company_id and then by total_amount.
ORDER BY company_id, total_amount DESC
  • Next, assign the company_id for each row to a variable
@comp_id := company_id
  • Finally, above this assignment, place the logic for either incrementing the ranking, or resetting it. This states that if the previous value of @comp_id is the same, then increase the rank (the next best sale day for the same company), otherwise reset to 1.
@rank := IF( @comp_id = company_id, @rank + 1, 1) AS day_rank,

3. Put it all together and sort
Now wrap the subquery, extracting only the columns needed and double sort again to get the same output as SQL Server.

SET @rank = 0;
SELECT
    company_name,
    sale_date,
    total_amount,
    day_rank
FROM (
    SELECT
        company_name,
        sale_date,
        total_amount,
        @rank := IF( @comp_id = company_id, @rank + 1, 1) AS day_rank,
        @comp_id := company_id
    FROM
        company_sales
    ORDER BY company_id, total_amount DESC
) rankings
ORDER BY company_name, day_rank

MySQL simplified

The query for MySQL can be simplified if you are not concerned about an extra column in the output(company_id) or about having the results properly sorted. They will still be ranked correctly, just not sorted by the database.

SELECT
    company_name,
    sale_date,
    total_amount,
    @rank := IF( @comp_id = company_id, @rank + 1, 1) AS day_rank,
    @comp_id := company_id AS company_id
FROM
    company_sales
ORDER BY company_id, total_amount DESC

produces output similar to below:

company_name sale_date total_amount day_rank company_id
Company A 2017-07-02 900 1 1
Company A 2018-03-01 500 2 1
Company A 2018-03-03 100 3 1
Company B 2018-03-03 1000 1 2
Company B 2017-09-15 700 2 2
Company B 2017-12-01 650 3 2
Company B 2017-06-10 560 4 2

Conclusion

By using this variable assignment and increment technique with proper sorting, we now have the ability to partition and assign row numbers to SELECT statements in MySQL. I hope this has been informative and adds yet another item to your MySQL toolbox!

Featured image by Nacho Capelo @ unsplash.com

0
Related Posts