ROW_NUMBER SQL Function Overview (2023)

In this article, we will discuss the SQL ROW_NUMBER function. This is a continuation of the SQL Fundamentals series. In this guide, I'll explain what window functions are and see sample examples to help you understand the concepts behind the SQL ROW_NUMBER function.

show

The most commonly used function in SQL Server is the SQL ROW_NUMBER function. The SQL ROW_NUMBER function is available in SQL Server 2005 and later versions.

ROW_NUMBER adds unique increment numbers to the result grid. The order in which row numbers are applied is determined by the ORDER BY expression. In most cases, one or more columns are specified in the ORDER BY expression, but more complex expressions or even subqueries can also be used. So it creates an ever-increasing integer value, always starting at 1, and subsequent rows get the next higher value.

You can also use it with a PARTITION BY clause. But when it crosses a partition limit or a limit, it resets the counter and starts at 1. So one partition can have values ​​1, 2, 3, etc., and the second partition starts the counter again from 1, 2 , 3.... etc., etc.

Basic:

1. The SQL ROW_NUMBER function is a non-permanent creation of a temporary sequence of values ​​and is calculated dynamically when the query is executed.
2. There is no guarantee that the rows returned by an SQL query that uses the SQL ROW_NUMBER function will be sorted in exactly the same order each time it is executed.
3. The ROW_NUMBER and RANK functions are similar. The output of ROW_NUMBER is a sequence of values ​​starting at 1 and incrementing by 1, and the value of the RANK function is also incremented by 1, but for a tie, the values ​​will be repeated.
4. If you have experience with Oracle, you will be more familiar with ROWNUM. It is a false pillar. It starts at 1 and increments by 1 until the end of the table.
5. The SQL ROW_NUMBER function is dynamic in nature and we can return the value using the PARTITION BY clause
6. The ORDER BY clause of the query and the ORDER BY clause of the OVER clause have nothing to do with each other.

pension

ROW_NUMBER

ROW_NUMBER followed by the OVER function and then an ORDER BY clause in parentheses. The ORDER BY clause is required to sort the result set.

Exceed

The OVER term defines the window or set of rows that the window function operates on, so it's important to understand this. Possible elements of the OVER clause are ORDER BY and PARTITION BY.

ORDER BY expressions with OVER clauses are supported when the rows must be ordered in a specific way for the function to work correctly.

Partition base

The Partition By clause is optional. When you specify a value, it will be divided byfromThe clause inserts the partition with the SQL ROW_NUMBER function applied. The values ​​specified in the PARTITION clause define the boundaries of the result set. If no PARTITION BY clause is specified, the OVER clause acts on all rows in the result set as a single data set. The clause can consist of one or more columns, more complex expressions, or even subqueries.

order by clause

Order by clause is a mandatory clause. Specifies the order and association of temporary values ​​with the rows of the specified partition. The ORDER BY clause is an expression of the OVER clause, which specifies how the function should order the rows in a specific way.

demonstration

In this section, we will learn about the SQL ROW_NUMBER function. Throughout the demo, I used the AdventureWorks2016 database.

How to use ROW_NUMBER in SQL queries

In the following example, we will see the use of the OVER clause.

Let's get a list of all customers by displaying columns like SalesOrderID, OrderDate, SalesOrderNumber, SubTotal, TotalDue, and RowNum. The Row_Number function is applied to the order of the CustomerID column. Temporary values ​​start at 1 assigned in the row of CustomerID and continue to the last row of the array. Since we did not specify an ORDER BY clause in the query, the order of CustomerID is not guaranteed.

How to use the Order by clause

The following example uses the ORDER BY clause in the query. The ORDER BY clause in the query is applied to the SalesOrderID column. We can see that the rows in the output are still sorted and returned. Row_Number still applies to CustomerID. The output shows that the ORDER BY of the query and the ORDER BY of the OVER clause are unrelated to the output.

How to use multiple columns with OVER clause

In the example below, you can see that we list the customer ID and order date in the ORDER BY clause. This provides the customer with the latest order details and the sequence of numbers assigned to the entire result set.

How to use SQL ROW_NUMBER function with PARTITION

The following example uses the PARTITION BY clause on the CustomerID and OrderDate fields. At the exit you can see the customer11019There were three orders in June 2014. In this case the partitioning is done on multiple columns.

The partition is a combination of OrderDate and CustomerID. Row_Number will start over for each unique combination of OrderDate and CustomerID. This way, it is easy to find customers who have placed multiple orders on the same day.

How to return a subset of rows using CTE and ROW_NUMBER

In the following example, we will parse the SalesOrderHeader to display the top five largest orders placed by each customer per month. Using the Month function, you can manipulate the orderDate column to get the month part. In this way, sales corresponding to a specific month (OrderDate) and customer (CustomerID) are shared.

To list the top five orders per month for each customer, a CTE is required. Create a window on the partitioned data, assign values ​​to it, and call the CTE to get the maximum order.

sum up

So far, we have looked at the SQL ROW_NUMBER function in detail. We have discussed many examples ranging from simple to complex. We also discussed how to use SQL ROW_NUMBER function with CTE (Common Table Expressions). In most cases, you will always see an over clause in every window function.

The over clause defines the window that each row views. In the over clause, there is a from partition supported by each window function, followed by a row by clause. That's all for now... We hope you enjoyed reading this article.