Row Number function in SQL Server

  • strict warning: Non-static method view::load() should not be called statically in /home/content/02/6751802/html/techpint/sites/all/modules/views/views.module on line 843.
  • strict warning: Declaration of views_plugin_display::options_validate() should be compatible with views_plugin::options_validate(&$form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_plugin_display_page::options_submit() should be compatible with views_plugin_display::options_submit(&$form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_plugin_display_block::options_submit() should be compatible with views_plugin_display::options_submit(&$form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_plugin_display_attachment::options_submit() should be compatible with views_plugin_display::options_submit(&$form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_field_broken::ui_name() should be compatible with views_handler::ui_name($short = false) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_sort_broken::ui_name() should be compatible with views_handler::ui_name($short = false) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_filter::options_validate() should be compatible with views_handler::options_validate($form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_filter::options_submit() should be compatible with views_handler::options_submit($form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_filter_broken::ui_name() should be compatible with views_handler::ui_name($short = false) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_filter_user_name::value_submit() should be compatible with views_handler_filter_in_operator::value_submit($form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_filter_user_name::value_validate() should be compatible with views_handler_filter::value_validate($form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Non-static method view::load() should not be called statically in /home/content/02/6751802/html/techpint/sites/all/modules/views/views.module on line 843.
  • strict warning: Declaration of views_handler_field_comment_username::init() should be compatible with views_handler_field::init(&$view, $options) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_filter_boolean_operator::value_validate() should be compatible with views_handler_filter::value_validate($form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_plugin_style_default::options() should be compatible with views_object::options() in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_plugin_row::options_validate() should be compatible with views_plugin::options_validate(&$form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_plugin_row::options_submit() should be compatible with views_plugin::options_submit(&$form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • strict warning: Non-static method view::load() should not be called statically in /home/content/02/6751802/html/techpint/sites/all/modules/views/views.module on line 843.
  • strict warning: Non-static method view::load() should not be called statically in /home/content/02/6751802/html/techpint/sites/all/modules/views/views.module on line 843.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.

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 

Result1.JPG

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 

Result3.JPG

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 

Result4.JPG

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

Thanks!

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 14 guests online.

Recent Answers

Anonymous added a answer to 301 Redirect in asp.net (17 weeks 3 days ago)
 
Anonymous added a answer to 301 Redirect in asp.net (17 weeks 3 days ago)
 
Anonymous added a answer to 301 Redirect in asp.net (17 weeks 5 days ago)
 
Anonymous added a answer to 301 Redirect in asp.net (17 weeks 5 days ago)
 
Anonymous added a answer to 301 Redirect in asp.net (17 weeks 6 days ago)
 
Anonymous added a answer to 301 Redirect in asp.net (17 weeks 6 days ago)