What is equivalent of Oracle ROWNUM in SQL Server?

We are executing openquery from SQL server to Oracle:

select * from emp where rownum<5

Its equivalent is:

with cte as 
(
select *, row_number() over(order by <column>) as rownum 
from emp
)
select * from cte
where rownum < 5

SQL server does not have rownum pseudo column.
So if we would like to fetch row number along with select statement we have to use a ranking function ROW_NUMBER or Top 1.

 

 

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