Understanding SQL ROW_NUMBER(): Syntax, Use Cases, and Examples

Key Takeaways

1. ROW_NUMBER() assigns unique sequential numbers to rows within partitions.
2. It's useful for ranking, pagination, and duplicate detection.
3. The ORDER BY clause is required in the function.

The ROW_NUMBER() function is a powerful window function in SQL that assigns a unique sequential integer to rows within a result set. This function is particularly useful for tasks such as ranking, pagination, and identifying duplicates. Let's delve into its syntax, usage, and practical examples.

Syntax

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression [ASC | DESC]
)

• ROW_NUMBER(): The function that assigns a sequential integer to each row.
• OVER: Defines the window or set of rows the function operates on.
• PARTITION BY (optional): Divides the result set into partitions to which the ROW_NUMBER() function is applied independently.
• ORDER BY: Specifies the order of the rows within each partition.

Use Cases

1. Assigning Sequential Numbers to Rows

To assign a unique number to each row in a result set:

SELECT
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    first_name,
    last_name,
    salary
FROM employees;

This query assigns a row number to each employee, ordered by descending salary.

2. Pagination

Implementing pagination by retrieving a subset of rows:

WITH numbered_employees AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY employee_id) AS row_num,
        first_name,
        last_name
    FROM employees
)
SELECT *
FROM numbered_employees
WHERE row_num BETWEEN 11 AND 20;

This retrieves rows 11 to 20 from the employees table.

3. Identifying Duplicates

To find duplicate records based on specific columns:

WITH duplicates AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS row_num
    FROM users
)
SELECT *
FROM duplicates
WHERE row_num > 1;

This identifies duplicate users based on the email field.

4. Top N per Group

To find the top N entries within each group:

WITH ranked_sales AS (
    SELECT
        salesperson_id,
        region,
        sales_amount,
        ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS rank
    FROM sales
)
SELECT *
FROM ranked_sales
WHERE rank <= 3;

This retrieves the top 3 salespersons in each region based on sales amount.

Considerations

• The ORDER BY clause is mandatory in the OVER clause for ROW_NUMBER().
• If PARTITION BY is omitted, the function treats the entire result set as a single partition.
• Unlike RANK() and DENSE_RANK(), ROW_NUMBER() assigns unique sequential numbers without gaps, even if there are ties in the ordering column.

Conclusion

The ROW_NUMBER() function is a versatile tool in SQL for assigning sequential numbers to rows, facilitating tasks like ranking, pagination, and duplicate detection. By understanding and leveraging this function, you can perform complex data analysis and manipulation with ease.

FAQs

What does SQL ROW_NUMBER() do?

It assigns a unique integer to each row based on the specified order.

How can I use ROW_NUMBER() for pagination?

Use it to assign numbers, then filter results using row numbers.

Does ROW_NUMBER() skip numbers with duplicate values?

No, it always generates consecutive numbers regardless of ties.

We are Leapcell, your top choice for hosting backend projects.

Leapcell is the Next-Gen Serverless Platform for Web Hosting, Async Tasks, and Redis:

Multi-Language Support

• Develop with Node.js, Python, Go, or Rust.

Deploy unlimited projects for free

• pay only for usage — no requests, no charges.

Unbeatable Cost Efficiency

• Pay-as-you-go with no idle charges.
• Example: $25 supports 6.94M requests at a 60ms average response time.

Streamlined Developer Experience

• Intuitive UI for effortless setup.
• Fully automated CI/CD pipelines and GitOps integration.
• Real-time metrics and logging for actionable insights.

Effortless Scalability and High Performance

• Auto-scaling to handle high concurrency with ease.
• Zero operational overhead — just focus on building.

Explore more in the Documentation!

Follow us on X: @LeapcellHQ