MySQL ROW_NUMBER Function: Guide from Basics to Alternatives

1. Introduction

Target Audience

• Beginners to intermediate users with basic SQL knowledge
• Engineers and data analysts who use MySQL for data processing and analysis
• Those considering migrating to the latest MySQL version

Benefits of the ROW_NUMBER() Function

2. What is the ROW_NUMBER function

Basic Syntax of the ROW_NUMBER Function

SELECT
    column_name,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY order_column) AS row_number
FROM
    table_name;

Meaning of Each Element

• ROW_NUMBER(): A function that assigns a sequential number to each row.
• OVER: The keyword used to define a window function.
• PARTITION BY: Groups the data by the specified column. It is optional; if omitted, the sequential numbers are applied to all rows.
• ORDER BY: Specifies the order in which numbers are assigned. This sets the sorting criteria.

Basic Example

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS row_num
FROM
    sales;

Result

How to Use PARTITION BY

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Result

Features and Considerations of the ROW_NUMBER() Function

• Unique numbering: Even with identical values, the sequential numbers remain unique.
• Handling of NULL values: When NULLs are present in ORDER BY, they appear first in ascending order and last in descending order.
• Performance impact: In large datasets, ORDER BY can be costly, so setting appropriate indexes is important.

3. Practical Use Cases

3-1. Ranking Within Groups

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

3-2. Extracting Top N Records

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT
    employee,
    department,
    sale
FROM
    RankedSales
WHERE
    rank <= 3;

3-3. Extracting and Deleting Duplicate Data

SELECT *
FROM (
    SELECT
        employee,
        sale,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
    FROM
        sales
) tmp
WHERE rank > 1;

DELETE FROM sales
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
        FROM
            sales
    ) tmp
    WHERE rank > 1
);

Summary

1. Ranking within groups
2. Extracting top N records
3. Detecting and deleting duplicate data

4. Comparison with Other Window Functions

4-1. RANK() Function

Basic Syntax

SELECT
    column_name,
    RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS rank
FROM
    table_name;

Example

SELECT
    employee,
    sale,
    RANK() OVER (ORDER BY sale DESC) AS rank
FROM
    sales;

• A and B, which have the same sales amount (800), are treated as rank 1.
• The next rank, 2, is skipped, so C becomes rank 3.

4-2. DENSE_RANK() Function

Basic Syntax

SELECT
    column_name,
    DENSE_RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS rank
FROM
    table_name;

Example

SELECT
    employee,
    sale,
    DENSE_RANK() OVER (ORDER BY sale DESC) AS dense_rank
FROM
    sales;

• A and B, with the same sales amount (800), are treated as rank 1.
• Unlike RANK(), the next rank starts at 2, preserving rank continuity.

4-3. Difference from ROW_NUMBER() Function

Example

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

• Even when values are identical, a unique number is assigned, so there are no duplicate ranks.
• It is suitable when you need strict control over data order or require row-level uniqueness.

4-4. Summary of Use Cases for Each Function

Summary

1. ROW_NUMBER() is suitable when a unique number is needed.
2. RANK() is useful when you want to assign the same rank to identical values while emphasizing gaps in ranking.
3. DENSE_RANK() is appropriate for scenarios that require continuous ranking without gaps.

5. Alternatives for MySQL versions below 8.0

5-1. Sequential numbering using user variables

Example: Sales ranking by department

SET @row_num = 0;
SET @dept = '';

SELECT
    employee,
    department,
    sale,
    @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
    @dept := department
FROM
    (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales;

5-2. Extracting the top N rows

SET @row_num = 0;
SET @dept = '';

SELECT *
FROM (
    SELECT
        employee,
        department,
        sale,
        @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
        @dept := department
    FROM
        (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales
) AS ranked_sales
WHERE rank <= 3;

5-3. Detecting and removing duplicate rows

SET @row_num = 0;
SET @id_check = '';

SELECT *
FROM (
    SELECT
        id,
        name,
        @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
        @id_check := name
    FROM
        (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
) AS tmp
WHERE rank > 1;

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
            @id_check := name
        FROM
            (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
    ) AS tmp
    WHERE rank > 1
);

5-4. Cautions when using user variables

1. Session dependency

• User variables are only valid within the session. They cannot be used in other queries or sessions.

1. Dependency on execution order

• User variables depend on the query execution order, so setting the ORDER BY clause correctly is crucial.

1. Readability and maintainability of SQL

• Because the code can become complex, using window functions is recommended for MySQL 8.0 and later.

Summary

6. Points to Note and Best Practices

6-1. Performance Considerations

1. ORDER BY Overhead

• Use indexes: Set indexes on columns used in ORDER BY to speed up sorting.
• Use LIMIT: Retrieve only the needed number of rows to reduce the amount of data processed.

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales
LIMIT 1000;

2. Increased Memory Usage and Disk I/O

• Split queries: Break the work into smaller queries and extract data step by step to reduce load.
• Use temporary tables: Store extracted data in a temporary table and perform aggregation from there to distribute the load.

6-2. Query Tuning Points

1. Checking the Execution Plan

EXPLAIN
SELECT
    employee,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

2. Index Optimization

• Single-column index: Applied for simple sort conditions
• Composite index: Effective when multiple columns are involved in conditions

CREATE INDEX idx_department_sale ON sales(department, sale DESC);

3. Leveraging Batch Processing

SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 0;
SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 1000;

6-3. Maintaining Data Consistency

1. Data Updates and Recalculation

CREATE VIEW ranked_sales AS
SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

6-4. Query Example Best Practices

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT *
FROM RankedSales
WHERE rank <= 3;

Summary

1. Optimize indexes to improve processing speed.
2. Check the execution plan to identify performance bottlenecks.
3. Address data updates and implement mechanisms to continuously maintain consistency.
4. Leverage batch processing and CTEs to distribute load.

7. Summary

7-1. Convenience of the ROW_NUMBER() Function

1. Sequential numbering per group: Easily achieve sales rankings by department or rankings by category.
2. Extracting top N rows: Efficiently retrieve and filter data based on specific criteria.
3. Detecting and removing duplicate data: Helpful for data organization and cleaning tasks.

7-2. Comparison with Other Window Functions

7-3. Strategies for Older Versions

• Reduced readability due to increased code complexity
• Optimization of query execution can be difficult
• Additional processing is required to maintain data integrity

7-4. Performance Optimization Tips

1. Leverage indexes: Create indexes on columns used in ORDER BY to improve processing speed.
2. Review execution plans: Use the EXPLAIN statement to pre‑validate performance.
3. Adopt batch processing: Break large datasets into smaller chunks to distribute load.
4. Utilize views and CTEs: Increase code reusability and simplify complex queries.

7-5. Conclusion

Appendix: Reference Resources

• Official documentation: MySQL Window Functions
• SQL execution environment: SQL Fiddle (an online tool for running and testing SQL)