Row Number function in SQL Server

I used to create temporary tables and subqueries to add a row number to every row in the result set. But somedays ago, I came across to the Row_Number() function provided in Sql Server, which simply constructs the row number column on the fly as part of the query. But a question might have arosed in your mind, What is the use of adding a Row_Number, when we have serial number associated with every row. There are several situation, where we need to query against the row number.(we can't query against a serial number)

Let's build a simple table of Book data to use as an example:

IF exists(SELECT OBJECT_ID('tempdb..#book'))
	BEGIN
		DROP TABLE #Book
	END
CREATE TABLE #Book(ID INT PRIMARY KEY, Author_Name VARCHAR(50),Book_Title VARCHAR(50)
		  ,Date_Publish SMALLDATETIME,CopieSold INT)
INSERT INTO #Book VALUES(1,'Dan Brown','Da Vinci Code','12/22/00',1000)
INSERT INTO #Book VALUES(2,'Paul Cohelo','The Alchemist','12/22/01',2000)
INSERT INTO #Book VALUES(3,'Paul Cohelo','Brida','12/22/02',5000)
INSERT INTO #Book VALUES(4,'Dan Brown','Angels & Demons','12/22/03',9000)
INSERT INTO #Book VALUES(5,'Dan Brown','The Lost Symbol','12/22/04',8000)
INSERT INTO #Book VALUES(6,'Agatha Christie','The Blue Train','12/22/05',7000)
INSERT INTO #Book VALUES(7,'Khaled Hosseini','The Kite Runner','12/22/06',10000)
INSERT INTO #Book VALUES(8,'Agatha Christie','Spider''s Web','12/22/07',3000)
INSERT INTO #Book VALUES(9,'Khaled Hosseini','The Thousand Splendid Suns','12/22/08',4000)
INSERT INTO #Book VALUES(10,'Chetan Bhagat','Five Point Someone','12/22/09',6000)
INSERT INTO #Book VALUES(11,'Chetan Bhagat','Two States','12/22/10',11000)
INSERT INTO #Book VALUES(12,'JD Salinger','The Catcher in the Rye','12/22/11',12000)

Now we have our simple table, which we can use to understand Row_Number.

Try to make queries to retrun

1. Latest Book of every Author.

2. Name the highest sold Book for every Author.

Yes, you can write these queries using a combination of temporary tables, sub-queries and cursors. But there is a better approach available using Row_Number function.

First Thing First: Lets look at the syntax of the Row_Number.

The basic syntax of the ROW_NUMBER() function requires an ORDER BY clause which defines the order in which the row number column values are produced.

SELECT ROW_NUMBER() OVER(ORDER BY Author_Name) AS [Row_Number]
,Author_Name,Book_Title,DATEPART(YEAR,Date_Publish) AS Date_Publish
,ID,CopieSold FROM #Book 

Just play a little bit using Order By clause on different fields and see the difference. You can also have a different Order by clause for a Select query.

Row Numbering and Partitioning : Real Power of row number can be visualize when used with partitioning. Partitioning is used to reset the row number back to 1. Let's say we wanted to show all the books with there Author arranged according to the copiesold of that book. 

SELECT ROW_NUMBER() OVER(Partition BY Author_Name ORDER BY CopieSold DESC)
 AS [Row_Number],Author_Name,Book_Title,DATEPART(YEAR,Date_Publish) AS Date_Publish
,ID,CopieSold FROM #Book 

By 'Partitioning By' Author_Name, every author get row numbers starting from 1. And the ORDER By Clause on CopieSold ensures that highest sold book of a author gets row number 1.

And now its simple to 'Name the highest sold Book for every Author', we just need to self reference this query using a Common Table Expression and filter by where Row_Number=1

;with cte
AS
(
SELECT ROW_NUMBER() OVER(Partition BY Author_Name ORDER BY CopieSold DESC) AS [Row_Number]
,Author_Name,Book_Title,DATEPART(YEAR,Date_Publish) AS Date_Publish
,ID,CopieSold FROM #Book 
)
SELECT Author_Name,Book_Title,Date_Publish,ID,CopieSold FROM cte WHERE [Row_Number]=1 

You can easily list the 'Latest Book of every Author' by putting order by clause on Date_Publish in the Row_Number function. Just try it, and let me know if it was easy or not.

Happy Querying