SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code

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.

SQL Row_Number() Over (Order By ...) sample query

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
Code

SQL Server Row_Number() Over (Partition By ... Order By ...)



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.