SQL Row_Number() Function Example Queries
SQL Row_Number() function is used to return a row number per row defined simply being ordered due to a column.
SQL Server Row_Number() function has two important parameters which provide a powerful development tool to SQL developers. These parameters are Order By clause where T-SQL programmers can define the ordering or sorting columns. The second parameter which is less known but most powerful is the Partition By clause which resets row number per each value change in defined columns.
Using SQL Row_Number() OVER (Partition By partitioncolumn Order By sortcolumn) SQL developers can create an ordered list of records grouped by a column.
Let's make a few SQL Row_Number() function example queries to understand easier.
Row_Number() with Order By Clause
As sample SQL data, I'll use sys.objects system view which is used to query system objects including tables, stored procedures, views, etc on the current SQL Server database.
SELECT [Row Number] = Row_Number() Over (Order By Name ASC),
type_desc, name
FROM sys.objects
As you see in below output, Row_Number() function successfully assigns an order number after sorting return data set according to the Order By clause. The first record is assigned as 1 with Row_Number() function.
The Order By clause defines the sorting rules and sort ascending the result set according to the Name column value. Of course a descending order can be done by using the DESC key after column name in Order By clause. And more than a single column can be used to order dataset. Please refer to referenced SQL tutorial for more information on how you can code in Transact-SQL using SQL Order By clause
Row_Number() with Partition By Clause
If you look carefully to the sql query return list, you may feel that a new numbering with each system object type column value might be more useful. This is a common scenerio for SQL programmers. Assume that you want to order your sales orders per sales representative according to the sales amounts. So it will be easier for you to see the maximum sales amount for each sales representative.
With our sample case where we fetch data from sys.objects system view, I'll demonstrate to numbering system objects according to their names and restarting row number starting from 1 when object type changes.
Here is the sample SQL statement with SQL Server Row_Number() Over (Partition By ...) clause
SELECT
[Row Number] = Row_Number() Over (Partition By type_desc Order By Name),
type_desc, name
FROM sys.objects
