SQL Lion

ROWNUM(): For each partition, it will return the sequence number starting from 1. It is very useful in case you want any functionality similar to identity column on the fly. As IDENTITY() will work only in case of select into statement i.e. when you are inserting records into some table and not in simple select statement.

Check Other Related Articles:

  T-SQL RowNumber() vs  Rank() vs DenseRank() vs NTILE()
  RANK ()
  DENSE_RANK()
  NTILE()

Let’s take the below query, where I want to have a RecID column in order to differentiate each record of the table employee.

select row_number() OVER(order by DeptNo) as RecID,* from dbo.employee;

Output:

I got a unique column named RecID which I can use further as per my requirement to get the desired result.

To get the 5^th record only:

select * from (
select row_number() OVER(order by DeptNo) as RecID,* from dbo.employee) InnQ
where InnQ.RecID = 5

Using <Partition By> in Row_Number():

But if I add one more functionality called <partition by> clause to Row_Number(), it will become more useful.  For example, I want to get the highest salary from each department, and then the query goes like this:

select * from (
select row_number() OVER(partition by DeptNo order by Salary desc) as RecID,* from dbo.employee) InnQ
where InnQ.RecID = 1

Output:

The <partition by> clause will divide the whole table into subsets based on DeptNo, and then <Order by> clause will order each subset (partition) on the basis of Salary column in decreasing order keeping the highest value to the top. Finally, the row_number will assign unique serial number to each partition starting from 1. In order to see how the query works, run the below query:

select row_number() OVER(partition by DeptNo order by Salary desc) as RecID,* from dbo.employee;

One more use of ROW_NUMBER () is to get the subset of rows from any desired point. Let’s consider this example where I want to show every odd record from the table.

select * from (
select row_number() OVER(order by DeptNo) as RecID,* from dbo.employee) InnQ
where InnQ.RecID % 2 <> 0

OUTPUT:

Check Other Related Articles:

  T-SQL RowNumber() vs  Rank() vs DenseRank() vs NTILE()
  RANK ()
  DENSE_RANK()
  NTILE()