SQL Server tutorial for beginners

Row Number function in SQL Server

sql server row_number example
sql server row number by partition
sql server row_number over partition by order by

In this video we will discuss Row_Number function in SQL Server. This is continuation to Part 108. Please watch Part 108 from SQL Server tutorial before proceeding.

Row_Number function
Introduced in SQL Server 2005
Returns the sequential number of a row starting at 1
ORDER BY clause is required
PARTITION BY clause is optional
When the data is partitioned, row number is reset to 1 when the partition changes

Syntax : ROW_NUMBER() OVER (ORDER BY Col1, Col2)

Row_Number function without PARTITION BY : In this example, data is not partitioned, so ROW_NUMBER will provide a consecutive numbering for all the rows in the table based on the order of rows imposed by the ORDER BY clause.

SELECT Name, Gender, Salary,
ROW_NUMBER() OVER (ORDER BY Gender) AS RowNumber
FROM Employees

Please note : If ORDER BY clause is not specified you will get the following error
The function ‘ROW_NUMBER’ must have an OVER clause with ORDER BY

Row_Number function with PARTITION BY : In this example, data is partitioned by Gender, so ROW_NUMBER will provide a consecutive numbering only for the rows with in a parttion. When the partition changes the row number is reset to 1.

SELECT Name, Gender, Salary,
ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Gender) AS RowNumber
FROM Employees

Use case for Row_Number function : Deleting all duplicate rows except one from a sql server table.

Discussed in detail in Part 4 of SQL Server Interview Questions and Answers video series.

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/rownumber-function-in-sql-server.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/09/rownumber-function-in-sql-server_30.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists