ROW_NUMBER and NTILE

ROW_NUMBER() and NTILE(n) are window functions used to analyze ordered data in SQL.

• ROW_NUMBER() assigns a unique number to each row according to the specified order.
• NTILE(n) divides rows into n nearly equal-sized groups, useful for quartiles, deciles, and other distributions.

Syntax

The basic syntax for ROW_NUMBER() and NTILE(n) is as follows:

ROW_NUMBER and NTILE syntax

SELECT 
  column1,
  ROW_NUMBER() OVER (ORDER BY column2) AS row_num,
  NTILE(n) OVER (ORDER BY column2) AS group_id
FROM table_name;

• OVER (ORDER BY ...) defines how the rows are sorted before numbering
• NTILE(n) distributes the ordered rows into n groups

Example: Learners' Scores

Suppose we track learners’ final_score in a course and want to assign row numbers and split them into quartiles based on their scores.

You can use ROW_NUMBER() and NTILE(4) to assign row numbers and group learners into quartiles:

Rank learners and group them into quartiles

SELECT name, final_score,
  ROW_NUMBER() OVER (ORDER BY final_score DESC) AS row_num,
  NTILE(4) OVER (ORDER BY final_score DESC) AS quartile
FROM course_progress;

Output:

name	final_score	row_num	quartile
Alex	98	1	1
Sara	95	2	1
Daniel	91	3	2
Mia	90	4	2
Emma	86	5	3
Noah	83	6	3
John	80	7	4
Leo	78	8	4

• ROW_NUMBER() assigns a unique number to each row according to the specified order.
• NTILE(4) divides the rows into four nearly equal-sized quartile groups.

How do ROW_NUMBER and NTILE differ?

• ROW_NUMBER() is best when you need a strict sequence or to uniquely identify rows (e.g., pagination, deduplication).
• NTILE(n) is best when you want to segment data into groups for analysis (e.g., quartiles, deciles, performance tiers).

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In