MySQL Row Number Window Function

Within MySQL, a ROW NUMBER() method contains a chronological number for every row inside the partition. It is just a window feature of some kind. The figure of rows begins at 1 with the figure of rows within the partition. Remember, before version 8.0, MySQL doesn’t permit the ROW NUMBER() function however, it offers a session variable that helps one imitate this feature. We will understand more about MySQL ROW NUMBER() functionality throughout this guide and produce a consecutive number for every row in the result collection. In MySQL, the ROW_NUMBER() methods are used with either the succeeding clauses:

The Over() clause will be used within it.
ORDERS BY clause arrange the result according to the sorting order of mentioned column.

Syntax:

>> SELECT col_name, ROW_NUMBER() OVER (PARTITION BY col_name, ORDER BY col_name) AS row_num FROM table_name;

Let us open the MySQL command-line client shell from the applications and type the password to log in.

You have to create a new table or use the default table to start working on the row number function. As presented in the below image, we have a table “animals” in the schema “data” with some records in it. Let’s fetch its records using the SELECT instruction.

>> SELECT * FROM data.animals;

Example 01: ROW_NUMBER() Using ORDER BY Clause

We will be using the same table to elaborate on some examples of the row number function. We are taking an example of the ROW_NUMBER() function followed by Over(), while only using the ORDER BY clause. We have been fetching all the records while numbering the rows according to the column “Price” order. We have given the name “row_num” to a column, which will store the row’s numbers. Let’s try the below command to do so.

>> SELECT *, ROW_NUMBER() OVER ( ORDER BY Price ) AS row_num FROM data.animals;

Upon executing the above query, we can see that the rows have been assigned with numbers according to the sorting order of the column “Price”. You might think that some smaller prices should be on the top of the column and it should sort according to that. But the ORDER BY clause only sees the first digit or alphabet of the column to sort values.

Let’s execute the same query followed by the ORDER BY clause while using the sorting order of the column “Age”. The output will be given according to the column “Age”.

>> SELECT *, ROW_NUMBER() OVER ( ORDER BY Age ) AS row_num FROM data.animals;

Example 02: ROW_NUMBER() Using PARTITION BY Clause

We will be using the only PARTITION BY clause in the ROW_NUMBER() query to check the results. We have been using the SELECT query to fetch the records followed by ROW_NUMBER() and OVER clause, while partitioning the table according to the column “Color”. Execute the appended below command in the command shell.

>> SELECT *, ROW_NUMBER() OVER ( PARTITION BY Color ) AS row_num FROM data.animals;

You can see in the result that the numbering of rows has been assigned in partitions, according to the colors sorting order. As we have 4 values for color “Black” that takes 4 rows. That’s why it has got four-row numbers starting from 1 to 4 and vice versa.

Try the same example, partitioned by the column “Gender” this time. As we know, we only have two genders in this table, that’s why 2 partitions will be formed. Females occupy 9 rows, that’s why it has row numbering from 1 to 9. While males have 8 values, that’s why it has 1 to 8.

>> SELECT *, ROW_NUMBER() OVER ( PARTITION BY Gender ) AS row_num FROM data.animals;

Example 03: ROW_NUMBER() Using PARTITION BY & ORDER BY

We have done the above two examples in the MySQL command-line, now it is time to do the ROW_NUMBER() example in the MySQL Workbench 8.0. So, open the MySQL Workbench 8.0 from the applications. Connect the MySQL Workbench with the local host root database to start working.

On the left side of the MySQL Workbench, you will find the Schema bar, blow the navigator. In this Schema bar, you will find the list of databases. Under the databases list, you will be having different tables and stored procedures, as you can see in the below image. We have different tables in our database ‘data’. We will be opening the table ‘order1’ using the SELECT command in the query area to start using it for ROW_NUMBER() function implementation.

>> SELECT * FROM data.order1;

The table “order1” has been displayed in the grid view as shown below. You can see it has 4 column fields, id, Region, Status, and OrderNo. We will be fetching all the records of this table while using the ORDER BY and PARTITION BY clause, both at the same time.

In the query area of MySQL Workbench 8.0, type the below-displayed query. The query has been started with the SELECT clause, fetching all the records followed by the ROW_NUMBER() function along with the OVER clause. After the OVER clause, we have specified the column “Status” proceeded by the “PARTITION BY” statement to divide the table into partitions according to this table. The ORDER BY clause is been used to arrange the table in descending way according to column “Region”. The row numbers will be kept in the “row_num” column. Tap on the flash icon to execute this command.

The below-displayed result will be shown. First of all, the table has been segregated into two parts as per the values of column “Status”. After that, it has been presented in the descending order of column ‘Region’ and the partitions have been assigned with the row numbers.

Conclusion:

Finally, we have completed all the necessary examples in using the ROW_NUMBER() function in MySQL Workbench and MySQL Command-line Client Shell.