Difference between ROW_NUMBER() OVER () & PARTITION BY clause

Difference between ROW_NUMBER () OVER () & PARTITION BY clause

 

 

This is one of the main basic and needed terms which you may have used in your daily uses in SQL Server and off course one of the main interview question.

 

Before jumping into code indeed lets understand what it means by ROW_NUMBER() & PARTITION BY clause.

Order by is required clause for using ROW_NUMBER(), as row number is generated on the basis of the column used along with ROW_NUMBER()

Example: Suppose there are 7 employees in your table 3 are from city Roorkee and others 4 are from city 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 in that case.

Please have a look on the example depicted below:

  1: declare @table table(
  2: 
  3: empid varchar(10),Name varchar(20), city char(10)
  4: 
  5: )
  6: 
  7: insert into @table 
  8: 
  9: select 'EMP001','Sachin Kalia', 'RoorKee'
 10: 
 11: union all select 'EMP002', 'Rohit Kalia', 'RoorKee'
 12: 
 13: union all select 'EMP003', 'Yogendra', 'RoorKee'
 14: 
 15: union all select 'EMP004', 'Ravish Sindhwani', 'Rohtak'
 16: 
 17: union all select 'EMP005', 'Parvinder', 'Rohtak'
 18: 
 19: union all select 'EMP006', 'Abhinav Singh', 'Noida'
 20: 
 21: union all select 'EMP006', 'Anshu Agarwal', 'Noida'
 22: 
 23: Select * from @table
 24: 
 25: 

After running the above sql statements here is the result.Now we will apply Order By and Partition By clause seperatly in order to understand the facts.

Now execute the given below lines one by one and see the actual facts.

  1: declare @table table(
  2: 
  3: empid varchar(10),Name varchar(20), city char(10)
  4: 
  5: )
  6: 
  7: insert into @table 
  8: 
  9: select 'EMP001','Sachin Kalia', 'RoorKee'
 10: 
 11: union all select 'EMP002', 'Rohit Kalia', 'RoorKee'
 12: 
 13: union all select 'EMP003', 'Yogendra', 'RoorKee'
 14: 
 15: union all select 'EMP004', 'Ravish Sindhwani', 'Rohtak'
 16: 
 17: union all select 'EMP005', 'Parvinder', 'Rohtak'
 18: 
 19: union all select 'EMP006', 'Abhinav Singh', 'Noida'
 20: 
 21: union all select 'EMP006', 'Anshu Agarwal', 'Noida'
 22: 
 23: --Select * from @table
 24: 
 25: SELECT *, ROW_NUMBER() OVER (ORDER BY empid ) As Counter
 26: 
 27: FROM @table
 28: 

The above given query will give the the following output :

One more clause that can be used with Over is PARTITION BY in case when you want to set one more level of filtration while generating Row_Number.Here is an example given below:

  1: declare @table table(
  2: 
  3: empid varchar(10),Name varchar(20), city char(10)
  4: 
  5: )
  6: 
  7: insert into @table 
  8: 
  9: select 'EMP001','Sachin Kalia', 'RoorKee'
 10: 
 11: union all select 'EMP002', 'Rohit Kalia', 'RoorKee'
 12: 
 13: union all select 'EMP003', 'Yogendra', 'RoorKee'
 14: 
 15: union all select 'EMP004', 'Ravish Sindhwani', 'Rohtak'
 16: 
 17: union all select 'EMP005', 'Parvinder', 'Rohtak'
 18: 
 19: union all select 'EMP006', 'Abhinav Singh', 'Noida'
 20: 
 21: union all select 'EMP006', 'Anshu Agarwal', 'Noida'
 22: 
 23: SELECT *, ROW_NUMBER() OVER (PARTITION BY city ORDER BY empid) As CounterByCityName FROM @table
 24: 
 25: 

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

Kindly find an attached SQL file.

Thanks

Categories: SQL Server