SQL server

Using row_number() for numbering rows.

There are times when tables have no unique numerical identifiers to use. I was in one such situation when we had a heap with 25 million rows and no identifier row. In these cases the row_number() function can be used.

Example TSQL:

select * from
(
select row_number() over (order by [name]) as row_num,name
from sys.databases
)as dbs
where
row_num=7

The row_number() has to be used with the over(). The over() tells which ordered column will have the row numbers associated with it. Note that we are using a nested select statement.

Advertisements
Standard

Thinking about someting? 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