sqlserver select where rownum between

[Kee Kee moon]

how to select where rownum between by using Microsoft sqlserver.

select * from mytable where rownum >= 50 and rownum <=100;

Please point out the web site I should post this message if this is not the right place to post sqlserver sql question.
Thanks

[Sagar Rohankar]

I think the above query works, still if you want BETWEEN thing just Google
sql server between

[Paul Sturrock]

Sagar Rohankar wrote:I think the above query works, still if you want BETWEEN thing just Google
sql server between

No, it won't. SQL Server doesn't support rownum.

Why do you need to know rownum? Could you not order by primary key and use that value instead?

[Jeanne Boyarsky]

This is the correct forum.

Even if you don't use primary key, you should sort by something if using rownum. Otherwise you run into trouble if there is server maintenance and the rows are returned in a different order.

[John Bengler]

Even if you use an Oracle DB which supports rownum this won't work...

In Oracle you can't select something with a where condition rownum > something...


There is one more thing to keep in mind when you use rownum and ordering: The rownum is created before the ordering is done...

This means e.g. by selecting

select *
  from stock_item
 where rownum <= 10
  order by price

you won't get the cheapest ten items, but just the first ten items sorted by price...

A possible solution in Oracle would look like this:

select * from (
select stock_item.*,rownum
  from stock_item
  order by price)
where rownum >= 5
    and rownum <= 10

You can find moreon this here: On ROWNUM and limiting results

But I don't know how to do this in SQLServer...

By Googling "microsoft sql server limit sql results" I just found a strange workaround, but I really can't imagine that this is the only way SQLServer supports:

It looks like SQLServer has a feature "select top x" which can be used by doing some sorting...:

select * from (
select top 10 emp_id,lname,fname from (
select top 30 emp_id,lname,fname
from employee
order by lname asc
) as newtbl order by lname desc
) as newtbl2 order by lname asc

But I'm not sure how performant such a solution for paging through resultsets will be...

John