Row Number, Partition, Rank And Dense Rank Function in Sql Server



Published On Thursday June 4, 2020
Reading Time: 2 minutes

For generating random number for a list of data on a table:

For generating unique numbers for a list of data on table we can use row_number method followed by order by of maintaining order on the basis of column name we would like to order from. A simple query can be as below and image with the query execution is below too.

select row_number() over (order by customername) as ordernumber, * from tblcustomer

Overview of Rank And Rows Function In Sql Server, Row Number, Partition, Rank And Dense Rank Function in Sql Server

 

For generating random number for a list of data on a table and group the ordering by any column value:

This is the condition where we need to use the partition method:

simple syntax can be

select row_number() over (partition by department order by customername) as departmentwisenumbering, * from tblcustomer

Overview of Rank And Rows Function In Sql Server, Row Number, Partition, Rank And Dense Rank Function in Sql Server


For generating unique number for individual customer as per the above example:

Similar to row_number but if order by column has same entry it will create different number for same customer so we rank function here.

select rank() over (order by customername) as uniqueuser, * from tblcustomer

Overview of Rank And Rows Function In Sql Server, Row Number, Partition, Rank And Dense Rank Function in Sql Server

For generating unique number for individual customer but with sequential number which lacks in rank function:

With the use of rank function though each function was assigned a unique id and in the above example the rajendra entry was assigned 1 as the id but when it went to the next sequence number it went to 3 i.e. it skipped number 2 so to over come it we use another function dense_rank

select dense_rank() over (order by customername) as uniqueuser, * from tblcustomer

Overview of Rank And Rows Function In Sql Server, Row Number, Partition, Rank And Dense Rank Function in Sql Server

So, this is all about these functions.

Screenshots: QuestPond YouTube Video

0 0 vote
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x