For an instructor lead, in-depth look at learning SQL click below.
ROW_NUMBER
is a built-in function of SQL that assigns a unique row number to each record present in the result set. This function counts every record in a result set and assigns a respective unique number to every row, starting with the value one for the first row. It serves the requirements of generating an ascending sequence number for each row in the record set.
Understanding ROW_NUMBER Function Syntax
The general syntax of the ROW_NUMBER function is as follows:
|
1 2 3 |
ROW_NUMBER () OVER ( [ PARTITION BY value_expression , ... [ n ] ] ORDER BY clause) |
Here:
- ‘PARTITION BY value_expression’ divides the result set into partitions. The ROW_NUMBER function will be applied to each partition separately and the row number will restart from 1 for each partition.
- ‘ORDER BY clause’ is used to order the rows in each partition. The ROW_NUMBER is assigned according to the ORDER BY order.
SQL ROW_NUMBER Function in Action
Let’s consider an example where you want to give a unique number to each user from the Users table, ordered by their first names. Your Users table may look like this:
|
1 2 3 4 5 6 7 |
CREATE TABLE Users ( UserID int, FirstName varchar(255), LastName varchar(255), ) |
You could write the ROW_NUMBER function like this:
|
1 2 3 4 |
SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY FirstName) AS RowNum FROM Users |
In this case, ROW_NUMBER is creating a new integer column called ‘RowNum’, and populates it with increasing integers, starting at 1, for each row, ordered by ‘FirstName’. For an example output, given names like Alice, Bob, Charlie, your result set would be:
|
1 2 3 4 5 6 |
'FirstName' 'LastName' 'RowNum' 'Alice' 'Smith' 1 'Bob' 'Johnson' 2 'Charlie' 'Brown' 3 |
In conclusion, the ROW_NUMBER function is a very handy feature when working with SQL, especially when dealing with large data sets where you need to assign unique row numbers or rank entries based on certain conditions. Happy querying!