Post
Ask Question

Difference Between ROW_NUMBER () OVER () & PARTITION BY Clause

Here we will explain the difference between ROW_NUMBER(), OVER() and PARTITION BY clauses. This is one of the basic and necessary terms that you may have used in your daily life when working on SQL Server and of course a question that is generally asked in interviews.

Here I've written a few lines of code to show it.

Before jumping into the code, let's understand what the ROW_NUMBER() and PARTITION BY clauses are.

ORDER BY is a required clause for using ROW_NUMBER() since row number is generated on the basis of the column used along with ROW_NUMBER().

Example

Assume there are 7 employees in your table, 3 are from the city Roorkee and the other 4 are from Rohtak and Noida respectively.

Now, if you would use "Order By empid" only, it would assign ROW_NUMBER() on the basis of empid in ascending order from 1, 2, 3, 4,5,6,7.

But, if you want to generate row numbers differently for cities then use PARTITION BY.

Please have a look at the following example:
  1. declare @table table(  
  2.     empid varchar(10),Name varchar(20), city char(10)  
  3. )  
  4.   
  5. insert into @table   
  6.     select       'EMP001','Sachin Kalia', 'RoorKee'  
  7. union all select 'EMP002', 'Rohit Kalia', 'RoorKee'  
  8. union all select 'EMP003', 'Yogendra', 'RoorKee'  
  9. union all select 'EMP004', 'Ravish Sindhwani', 'Rohtak'  
  10. union all select 'EMP005', 'Parvinder', 'Rohtak'  
  11. union all select 'EMP006', 'Abhinav Singh', 'Noida'  
  12. union all select 'EMP006', 'Anshu Agarwal', 'Noida'  
  1. Select * from @table  
After running the preceding SQL statements here is the result. Now we will apply Order By and Partition By clauses separately to understand the facts.

table

Now execute the following lines one by one and see the actual facts.
  1. declare @table table(  
  2.     empid varchar(10),Name varchar(20), city char(10)  
  3. )  
  4.   
  5. insert into @table   
  6.     select       'EMP001','Sachin Kalia', 'RoorKee'  
  7. union all select 'EMP002', 'Rohit Kalia', 'RoorKee'  
  8. union all select 'EMP003', 'Yogendra', 'RoorKee'  
  9. union all select 'EMP004', 'Ravish Sindhwani', 'Rohtak'  
  10. union all select 'EMP005', 'Parvinder', 'Rohtak'  
  11. union all select 'EMP006', 'Abhinav Singh', 'Noida'  
  12. union all select 'EMP006', 'Anshu Agarwal', 'Noida'  
  1. --Select * from @table  
  2. SELECT *, ROW_NUMBER() OVER (ORDER BY empid ) As Counter  
  3. FROM @table  
The preceding query will provide the following output:

query output

One more clause that can be used with OVER is PARTITION BY for use when you want to set one more level of filtration when generating Row_Number. Here's an example:
  1. declare @table table(  
  2.     empid varchar(10),Name varchar(20), city char(10)  
  3. )  
  4.   
  5. insert into @table   
  6.     select       'EMP001','Sachin Kalia', 'RoorKee'  
  7. union all select 'EMP002', 'Rohit Kalia', 'RoorKee'  
  8. union all select 'EMP003', 'Yogendra', 'RoorKee'  
  9. union all select 'EMP004', 'Ravish Sindhwani', 'Rohtak'  
  10. union all select 'EMP005', 'Parvinder', 'Rohtak'  
  11. union all select 'EMP006', 'Abhinav Singh', 'Noida'  
  12. union all select 'EMP006', 'Anshu Agarwal', 'Noida'  
  1. SELECT *, ROW_NUMBER() OVER (PARTITION BY city ORDER BY empid) As CounterByCityName FROM @table  
PARTITION BY

This is the beauty of these keywords and may be utilized to make the rows sequential in manner.

Kindly find an attached SQL file.

Thanks!


Similar Articles