Category Archives: SQL

What is difference between ROW_NUMBER() , RANK() and DENSE_RANK()?

All of these three functions are used to calculate RowID for the result set returned from a query but in slightly different way.

Row_Number(): This function will assign a unique id to each row returned from the query.

Consider following Query

DECLARE @Table TABLE (

Col_Value varchar(2)

)

INSERT INTO @Table (Col_Value)

VALUES (‘a’),(‘a’),(‘a’),(‘b’),(‘b’),(‘c’),(‘c’);

 

SELECT

Col_Value,

ROW_NUMBER() OVER (ORDER BY Col_Value) AS ‘RowID’

FROM

@Table;

After executing it we will get

Col_Value RowID
A 1
A 2
A 3
B 4
B 5
C 6
C 7

As we notice, each and every row has a unique ID.

Rank(): This function will assign a unique number to each distinct row, But it leaves gap between the groups. Let me explain with a query, we will use the same query we used above with Rank().

SELECT

Col_Value,

Rank() OVER (ORDER BY Col_Value) AS ‘RowID’

FROM

@Table;

This query will return

Col_Value RowID
a 1
a 1
a 1
b 4
b 4
c 6
c 6

As we can see rowid is unique for each distinct value, but with gap. What is this Gap?

This Gap represents number of occurrence. For example: – value ‘a’ is repeated thrice and has rank ‘1’ the next rank will be 1+3=4. Same with the next value 4+2=6 and so on.

Dense_Rank(): This function is similar to Rank with only difference, this will not leave gaps between groups.

So if we use same query used for Rank then:-

SELECT

Col_Value,

DENSE_RANK() OVER (ORDER BY Col_Value) AS ‘RowID’

FROM

@Table;

We will get

Col_Value RowID
a 1
a 1
a 1
b 2
b 2
c 3
c 3

So it is clear that, it generates unique id for each group and without repetition.

As we are clear now what these functions do we can use them in different scenarios based on our need.

For Example: – Row_Number() can be used if we want to skip and fetch some records. Take a look on this query:-

WITH AllRecords

AS (

SELECT

Col_Value,

ROW_NUMBER() OVER (ORDER BY Col_Value) AS ‘RowID’

FROM

@Table)

SELECT

Col_Value,

RowID

FROM

AllRecords

WHERE

RowID >= 4

AND RowID <= 5;

This will retun only those rows with RowID  4 and 5. This function is very useful in paging data in SQL instead of in code till SQL SERVER 2012.  In SQL Server 2012 Microsoft introduces new feature Offset Fetch similar to what we did in above query.

 

In SQL Server 2012 above query will be written as

SELECT

Col_Value

FROM

@Table

ORDER BY

Col_Value OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY;

This will skip first 3 rows and will fetch next 2.

Similarly We can use Dense_Rank() when we need to calculate rowid with Select Distinct.

SELECT

DISTINCT

Col_Value,

DENSE_RANK() OVER (ORDER BY Col_Value) AS ‘RowID’

FROM

@Table;

Will return:-

Col_Value RowID
a 1
b 2
c 3

Or when we need a report where ranks clashes. For example:- two students scored same marks and shares same division.

We can use Rank() if we don’t want consecutive numbers.

Hope this article helped you.

Basic points to be considered when starting with SQL Server – – Part 4

Hello Friends, Yesterday I was fetching some records from table and Then I thought about ‘*’.

Is asterisk is our friend or enemy ?

Read the rest of this entry

Basic points to be considered when starting with SQL Server – – Part 3

Hello friends, in our last few articles we were discussing about data types, benefits of correct selection. Today, we will discuss about “SET NOCOUNT”.
Read the rest of this entry

Basic points to be considered when starting with SQL Server – – Part 2

Hello friends, In the previous post, we discussed when to use which datatype for string values.
Today, I want to draw your attention to some more data type selection. I mean we have several data types in SQL like:-

Read the rest of this entry

Basic points to be considered when starting with SQL Server – – Part 1

I have been working with SQL from some time and I know how it feels when we start using it first time. Several questions arises in our mind like, Am I writing queries in correct way? What impact on my front end performance? What should I use? etc.

Let’s start answering these questions.

Read the rest of this entry

Some Important T-SQL Programmability feature of SQLSERVER 2008

There are lots of new feature introduced with SQLServer 2008 for improving the SQL experience of developers. Here we will cover some very important and useful features of SQLServer 2008. Features which we are going to cover is –

  1. Declaring and initializing new variables.
  2. Compound assignment operators
  3. Table value constructor
  4. Enhanced Convert Function
  5. Date and Time Data types
  6. Table Type

Read the rest of this entry

%d bloggers like this: