Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sqlserver select where rownum between

 
Kee Kee moon
Ranch Hand
Posts: 147
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 2907
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think the above query works, still if you want BETWEEN thing just Google
sql server between
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34410
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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



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:



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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic