How to Rank Data in SQL: An Introduction to Window Functions
Grace Collins
Solutions Engineer · Leapcell

Key Takeaways
- SQL provides several ranking functions to order and analyze data.
- Choose the right function based on how you want to handle ties.
- 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?
Function | Handles Ties | Gaps After Ties | Use Case |
---|---|---|---|
ROW_NUMBER() | No | No (always 1,2,3...) | When each row must be uniquely numbered |
RANK() | Yes | Yes | When ties share rank and gaps are acceptable |
DENSE_RANK() | Yes | No | When ties share rank, but gaps are not allowed |
NTILE(N) | N/A | N/A | When dividing rows into equal-sized buckets |
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
- Choose window function based on tie handling and gap preferences.
- Use
PARTITION BY
for grouping. - Use
ORDER BY
to define ranking logic. - Filter results using
WHERE
or CTE for targeted outcomes.
FAQs
They are used to order and analyze data within result sets.
RANK() leaves gaps after ties; DENSE_RANK() does not.
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