Row_number() Function with no specific order

You know that Row_number() function is used to generate row number to rows based on the column which is ordered

What if you want to generate row number without ordering any column

Here is the method

Consider the following dataset

use tempdb 

create table temp(dates datetime)
 
insert into temp
select '20090114' union all
select '20080131 10:10:10' union all
select '20070111' union all
select '20090330' union all
select '20120229' union all
select '20100228'

Now you need to use row_number function without order dates column

Here are two methods

1 Assign dummy column with literal 0 and order it by that column

select 
       row_number() over(order by order_col) as sno ,
       dates from 
(
        select dates,0 as order_col from temp
) as t

2 Assign dummy column with literal 0 directly on the Order by clause

select 
      row_number() over(order by (select 0)) as sno ,
      dates 
from 
      temp 

Both the queries will return the following result

sno                  dates
-------------------- -----------------------
1                    2009-01-14 00:00:00.000
2                    2008-01-31 10:10:10.000
3                    2007-01-11 00:00:00.000
4                    2009-03-30 00:00:00.000
5                    2012-02-29 00:00:00.000
6                    2010-02-28 00:00:00.000
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s