How to Rank Data in SQL: An Introduction to Window Functions

Key Takeaways

1. SQL provides several ranking functions to order and analyze data.
2. Choose the right function based on how you want to handle ties.
3. Use PARTITION BY to rank within specific groups.

Introduction

Ranking in SQL is essential for ordering data, handling ties, and filtering results based on position. SQL provides several window functions—ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()—to achieve these goals. Each serves slightly different use cases.

---

1. ROW_NUMBER()

• Assigns a unique sequential number to each row, without considering ties.

• Syntax:

  ROW_NUMBER() OVER (
    [PARTITION BY partition_expr]
    ORDER BY order_expr [ASC|DESC]
  ) AS row_num

• Example: Assign unique row numbers by descending score:

  SELECT 
    student_name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
  FROM students;

---

2. RANK()

• Assigns the same rank to tied values, but leaves gaps in subsequent ranks.

• Syntax:

  RANK() OVER (
    [PARTITION BY partition_expr]
    ORDER BY order_expr [ASC|DESC]
  ) AS rank

• Example: Rank students, allowing gaps:

  SELECT 
    student_name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
  FROM students;

  If two students share the top score, both are rank 1, and the next is rank 3 .

---

3. DENSE_RANK()

• Similar to RANK(), but does not leave gaps after ties.

• Syntax:

  DENSE_RANK() OVER (
    [PARTITION BY partition_expr]
    ORDER BY order_expr [ASC|DESC]
  ) AS dense_rank

• Example:

  SELECT 
    student_name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
  FROM students;

  If two students tie for first, both rank 1, and the next is rank 2 .

---

4. NTILE(N)

• Divides rows into N buckets and assigns bucket numbers 1 through N.

• Syntax:

  NTILE(N) OVER (
    [PARTITION BY partition_expr]
    ORDER BY order_expr [ASC|DESC]
  ) AS tile

• Example:

  SELECT 
    student_name,
    score,
    NTILE(4) OVER (ORDER BY score DESC) AS quartile
  FROM students;

  Divides students into four groups by score .

---

5. Partitioning

• PARTITION BY allows ranking within groups (e.g., per department or subject).

• Example: Rank students within each class:

  SELECT
    class,
    student_name,
    score,
    RANK() OVER (
      PARTITION BY class
      ORDER BY score DESC
    ) AS class_rank
  FROM students;

---

6. Why Choose One?

---

7. Filtering Top‑N Results

To get, say, the top 3 students using RANK():

WITH ranked AS (
  SELECT
    student_name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
  FROM students
)
SELECT student_name, score
FROM ranked
WHERE rank <= 3;

This query includes ties. Want exactly 3 rows? Use ROW_NUMBER() instead .

---

8. Real-World Examples

• Sales per store per product:

  SELECT
    product,
    store_id,
    sales,
    RANK() OVER (
      PARTITION BY product
      ORDER BY sales DESC
    ) AS sales_rank
  FROM sales_data;

  Helps identify top-selling stores per product .

• Department salary analysis:

  WITH dept_ranked AS (
    SELECT
      department_id,
      employee_name,
      salary,
      RANK() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
      ) AS dept_rank
    FROM employees
  )
  SELECT *
  FROM dept_ranked
  WHERE dept_rank = 1;

  Finds highest-paid employee per department .

---

9. Summary

1. Choose window function based on tie handling and gap preferences.
2. Use PARTITION BY for grouping.
3. Use ORDER BY to define ranking logic.
4. Filter results using WHERE or CTE for targeted outcomes.

FAQs

What is the main purpose of ranking functions in SQL?

They are used to order and analyze data within result sets.

What is the difference between RANK() and DENSE_RANK()?

RANK() leaves gaps after ties; DENSE_RANK() does not.

How can you rank data within groups in SQL?

Use the PARTITION BY clause in your window function.

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