RSS

RANK() vs. ROW_NUMBER() in MS SQL Server

23 Oct

Sometimes we ask ourselves some questions:

  • How can I generate a new column represents the number of row in SELECT statements ?
  • How can I rank the rows retrieved as a result set from SELECT statement ?
  • What are the differences  between RANK and ROW_NUMBER ?

I will answer now … Follow me.


ROW_NUMBER() .. !!

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

— Create the table.

CREATE TABLE [dbo].[Employees](
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[MobileNumber] [varchar](50) NULL
) ON [PRIMARY]

——————–

— Insert Data.

INSERT INTO dbo.Employees VALUES (‘Ahmed’,’Negm’,’+201119777244′)
INSERT INTO dbo.Employees VALUES (‘Amira’,’Muhammad’,NULL)
INSERT INTO dbo.Employees VALUES (‘Ola’,’Maher’,NULL)
INSERT INTO dbo.Employees VALUES (‘Hazem’,’Youssuf’,NULL)
INSERT INTO dbo.Employees VALUES (‘Kamel’,’Abdul Rahman’,Null)
INSERT INTO dbo.Employees VALUES (‘Hana’,’Negm’,’+201209447949′)

Now, you have a table included some of rows. And we need to retrieve these rows and renumber it .. follow the the pictures and T-SQL commands … If you execute this statement: “SELECT * FROM dbo.Employees“, you will see the next result:-

But if you want to make new column in your result set represents the row’s number use the (ROW_NUMBER) as following:

SELECT ROW_NUMBER() OVER(ORDER BY FirstName ASC) AS ‘Row Number’, * FROM dbo.Employees;

— Here you have a new result set included all rows and columns in addition to the number of each row depended on sorting the FirstName column in ascending order.

You will see the next result set:-

RANK() … !!

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

If two or more rows tie for a rank, each tied rows receives the same rank. For example, if the two top employees have the same salary value, they are both ranked one. The employee with the next highest salary is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.

The sort order that is used for the whole query determines the order in which the rows appear in a result set.

— Create table

CREATE TABLE [dbo].[Employees](
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[MobileNumber] [varchar](50) NULL,
[Salary] [float] NULL
) ON [PRIMARY]

———————————————

— Insert Data

INSERT INTO dbo.Employees VALUES (‘Ahmed’,’Negm’,’+201119777244′, 5000)
INSERT INTO dbo.Employees VALUES (‘Amira’,’Muhammad’,NULL, 2600)
INSERT INTO dbo.Employees VALUES (‘Ola’,’Maher’,NULL, 3500)
INSERT INTO dbo.Employees VALUES (‘Hazem’,’Youssuf’,NULL, 4000)
INSERT INTO dbo.Employees VALUES (‘Kamel’,’Abdul Rahman’,Null, 3500)
INSERT INTO dbo.Employees VALUES (‘Hana’,’Negm’,’+201209447949′, 5500)

To do ranking and give a rank for the retrieved rows, try with this code:

SELECT RANK() OVER(ORDER BY Salary DESC) AS ‘Employee”s Rank’, * FROM dbo.Employees

— Here you have a new result set included all rows and columns in addition to the rank of each employee/row depended on sorting the Salary column in descending order.

— Notice please, the following picture represents the result set, there are two rows have the same rank because these employees have the same salary.

Enjoy your time .. !!

 
Leave a comment

Posted by on October 23, 2011 in MS SQL Server

 

Tags: , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: