SQL Server Performance Forum – Threads Archive

Obtain data by value ranks

Hello, I would like to obtain data by value ranks, I mean,
select [1-10 rows] from table
select [11-20 rows] from table In Oracle that it’s possible with rownum and two subqueries and in Access
I make it with a query like this: Select lastname from
(Select top 10 lastname from
(Select top 20 lastname from employees order by lastname asc)
order by lastname desc
order by lastname asc I tried the same query in SQL Server but you can’t uses an order by in a subquery.
And I tried this too but it doesn’t work either: SELECT RowNumber=(SELECT COUNT(*)
FROM employees
WHERE lastname <= T.lastname),
lastname
FROMemployees T
ORDER BY lastname Because I obtain: when I want to obtain 2 ALLEN 1 ALLEN
2 ALLEN 2 ALLEN
3 CARR 3 CARR
4 SMITH 4 SMITH
4 SMITH 5 SMITH And after that ask for the desired rows. Thanks in advance Sonia

In SQL 2000 this function isn’t available – however it has been introduced in 2005 (as ROW_NUMBER()). In 2000 the best way to solve the problem is to insert the data into a temp table which has an identity column on it. You can then select by row number. Hope this helps.

quote:Originally posted by sgfbio
I tried the same query in SQL Server but you can’t uses an order by in a subquery.

You can use order by in subquery if top clause is involved.
You can use: Select b.lastname
from (Select top 10 lastname
from (Select top 20 e.lastname from employees e order by e.lastname asc) as a
order by a.lastname desc) as b
order by b.lastname asc
however I’m not sure if it is the most efficient solution.

Also refer this
http://www.aspfaq.com/show.asp?id=2120 Madhivanan Failing to plan is Planning to fail

Madhivanan’s link sums it up very well. This is a FAQ. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

]]>