MySQL: ROW_NUMBER() reference

ROW_NUMBER() is a MySQL window function that assigns a sequential number to each row in a result set.

Syntax

ROW_NUMBER() OVER (<partition_definition> <order_definition>)

<partition_definition>

You can use the <partition_definition> to partition the result set into groups (see MySQL: PARTITION BY reference). <partition_definition> is optional and if omitted, the entire result set is considered as a single partition.

<order_definition>

Used to determine the ordering of rows.

Example

Suppose you had the following email_log table:

And you want to get the list of users ordered by number of emails like so:

You can use ROW_NUMBER() to assign a ranking to each user based on the number of emails they sent:

select from_user_id,
       count(*) as                                total_emails,
       row_number() over (order by count(*) desc) ranking
from email_log
group by from_user_id

In the above example, we omit the <partition_definition> from ROW_NUMBER() to treat the whole result set as a single partition. We then assign a sequential number to each row using the (order by count(*) desc) order.

This means that we start with the record that has the highest value for count(*) desc and give it the sequential number 1. Then we move on to the next record with the highest value for count(*) desc and assign it the sequential number 2. And so on.

If you change (order by count(*) desc) to (order by count(*) asc), the ranking changes accordingly: