This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Java Interview Guide and have Anthony DePalma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes sqlserver select where rownum between Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "sqlserver select where rownum between" Watch "sqlserver select where rownum between" New topic

sqlserver select where rownum between

Kee Kee moon
Ranch Hand

Joined: Dec 11, 2009
Posts: 147
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.
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2904

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

[LEARNING bLOG] | [Freelance Web Designer] | [and "Rohan" is part of my surname]
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

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?

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33132

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
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...

I agree. Here's the link:
subject: sqlserver select where rownum between
It's not a secret anymore!