Search content within the blog

Saturday, May 14, 2011

Transact-SQL :- ROW_NUMBER()

Transact-SQL :- ROW_NUMBER()

While creating query we may need a column which will hold the sequential number like 1, 2, 3… Also we may need to show that sequential number according to a particular partition. In that situation we can use the ‘ROW_NUMBER’ keyword.

Below syntax can be followed :-


1. To get sequential number irrespective of any partition.

ROW_NUMBER ( ) OVER (<order_by_clause>)

SELECT ROW_NUMBER() OVER (ORDER BY [Col2] ASC) AS RowNo,
[Col1],[Col2],[col3] FROM [dbo].[demo_table];

Result will be:

RowNo Col1 Col2 col3
-------------------- -------------------- --------------------------------------------------
1 4 Andew 2009-03-01 00:00:00.000
2 8 Andew NULL
3 6 James 2009-07-01 00:00:00.000
4 3 John 2009-02-01 00:00:00.000
5 1 John 2009-06-24 00:00:00.000
6 2 Lee 2009-06-24 00:00:00.000
7 5 Steven 2009-06-24 00:00:00.000

2. To get sequential number within a partition.
ROW_NUMBER ( ) OVER (<partition_by_clause><order_by_clause>)

SELECT ROW_NUMBER() OVER (PARTITION BY [Col2] ORDER BY [Col2] ASC) AS RowNo,[Col1],[Col2],[col3] FROM [demo_table];

Result will be:

RowNo Col1 Col2 Col3
------------------- -------------------- ---------------------------------------------------
1 4 Andew 2009-03-01 00:00:00.000
2 8 Andew NULL
1 6 James 2009-07-01 00:00:00.000
1 3 John 2009-02-01 00:00:00.000
2 1 John 2009-06-24 00:00:00.000
1 2 Lee 2009-06-24 00:00:00.000
1 5 Steven 2009-06-24 00:00:00.000

No comments:

Post a Comment