Main Window Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

Last lecture, we figured out why window functions are even a thing. Now let’s check out some specific functions and what their results look like. We’ll get into the syntax details in the next lecture.

The ROW_NUMBER() Function

The ROW_NUMBER() function gives a unique number to every row within a window. Basically, it just numbers the rows in the order you set in ORDER BY.

Syntax:

ROW_NUMBER() OVER ([PARTITION BY column] ORDER BY column)

Where:

• PARTITION BY column (optional): splits your data into groups. If you skip it, the numbering is global for the whole set.
• ORDER BY column: sets the order for numbering the rows.

Example. Numbering rows in a table

Let’s look at a students table with info about students and their grades.

SELECT * FROM students;

Now let’s number the rows in descending order of their grades (score):

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

Result:

Each row got a unique number — based on sorting by descending grades.

This is a simple but powerful move — adding a row number to your query result. In classic SELECT you just can’t do this without window functions.

The RANK() Function

The RANK() function is a lot like ROW_NUMBER(), but it takes ties into account. If rows have the same value in the sort, they get the same rank, and the next rank is skipped.

Syntax:

RANK() OVER ([PARTITION BY column] ORDER BY column)

Example. Ranking students by their grades

Let’s use RANK() on the same data:

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

Result:

Here, rows with the same value (95 and 87) got the same rank, and the next ranks were skipped.

The DENSE_RANK() Function

DENSE_RANK() is like RANK(), but it doesn’t skip rank values. That means if there are ties, the next rank is just one more than the previous.

Syntax:

DENSE_RANK() OVER ([PARTITION BY column] ORDER BY column)

Example. Dense ranking

Let’s use DENSE_RANK() on the same data:

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

Result:

Here, unlike RANK(), the rank values just go up without any gaps.

The NTILE() Function

The NTILE() function splits rows into equal groups (quantiles) and gives each row a group number.

Syntax:

NTILE(n) OVER ([PARTITION BY column] ORDER BY column)

• n: how many groups you want to split the data into.

Example. Splitting students into 3 groups

Let’s split the students into 3 groups by descending grades:

SELECT
    name, 
    score, 
    NTILE(3) OVER (ORDER BY score DESC) AS group_num
FROM students;

Result:

Heads up: if you can’t split the rows into perfectly equal groups, the extra rows go into the first groups. In this example, the first two groups have two rows each, and the last one just has one.

When should you use which function?

• ROW_NUMBER(): for unique numbering of rows in a sorted order.
• RANK(): for ranking with ties and skipping the next rank.
• DENSE_RANK(): for ranking with ties but without skipping ranks.
• NTILE(): For evenly splitting rows into groups.

All these functions help you analyze data on a whole new level. Use them whenever you need flexibility in calculating row numbers or splitting up your data.