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'))
		  ,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.

,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
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

Anonymous's picture

very informative article.

very informative article. Thanks for sharing.

Anonymous's picture

Fantastic article. Great

Fantastic article. Great stuff.

Anonymous's picture


Spent the past day or 2 trying to figure out the exact syntax and how to put it into practice, thanks for the blog, very easy to understand and well explained!  

Post new comment

The content of this field is kept private and will not be shown publicly. If you have a Gravatar account, used to display your avatar.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <p> <span> <div> <h1> <h2> <h3> <h4> <h5> <h6> <img> <map> <area> <hr><br> <br /> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <em> <b> <u> <i> <strong><font> <del> <ins> <sub> <sup> <quote> <blockquote> <pre> <address> <code> <cite> <embed> <object> <param> <strike> <caption>
  • You can enable syntax highlighting of source code with the following tags: <c>, <cpp>, <csharp>, <drupal5>, <drupal6>, <java>, <javascript>, <mysql>, <php>, <python>, <ruby>, <sql>, <tsql>, <vbnet>, <xml>. Beside the tag style "<foo>" it is also possible to use "[foo]". PHP source code can also be enclosed in <?php ... ?> or <% ... %>.
  • Lines and paragraphs break automatically.

More information about formatting options

Who's online

There are currently 0 users and 3 guests online.

Recent Answers

Anonymous added a answer to 301 Redirect in (10 hours 40 min ago)
Anonymous added a answer to 301 Redirect in (1 day 6 hours ago)
Anonymous added a answer to 301 Redirect in (1 day 15 hours ago)
Anonymous added a answer to 301 Redirect in (1 day 21 hours ago)
Anonymous added a answer to 301 Redirect in (4 days 11 hours ago)
Anonymous added a answer to LC3 Assembly Palindrome Program (3 years 28 weeks ago)