Top N means absolutely nothing without an order by, you are not guaranteed
order even if you have a clustered index without an order by.

--
Ray Higdon MCSE, MCDBA, CCNA
---
[quoted text, click to view]
Hi!

Thank you for your reply.
It doesn´t matter. The query is

SELECT * FROM MONEY

When i run this query i get 100 rows. But i want to see
rows between 20 and 40. I can see top 20. It´s easy

SELECT TOP 20 FROM MONEY

But my question is, is it possible to run a query to get
row between 20 and 40 or 40 and 60 etc.

Mybe like this

SELECT BETWEEN 20 TO 40 FROM MONEY :-)

Thank you once again for your reply
Kind regards
Mehran
[quoted text, click to view]

hi mehran,

[quoted text, click to view]

top clause is meaningless if not specified with the order by clause.
Otherwise retrieval order of the row is not guaranteed and any row will be
retrieved from the table.

There are various methods to accomplish what you are looking for.

Following example will retrieve the top 20 orders on the basis of the amount
of frieght. (im trying to show you the importance of "order by" clause to
get the meaningful result.)

select top 20 orderid,freight from orders
order by freight desc

--following query will give you the rows from 20 to 40 on the basis of
freight amount.

select top 20 orderid,freight from orders
where orderid not in
(select top 20 orderid from orders o
order by freight desc)
order by freight desc

having said that, it is important to know that you can not use variable in
the top clause. ie, if you try something as follows, it will error out.

declare @top int
set @top=20
select top @top orderid,freight from orders
order by freight

There are many workarounds to this. I will show you 2 of them below.

--(1)
declare @from int,@to int
select @from=20, @to=25

select orderid,freight from
(select orderid,
freight,
(select count(distinct freight) from orders a
where a.freight >= b.freight) rnk
from orders b) X
where rnk > @from
and rnk <= @to
order by rnk

--(2)dynamic sql

declare @from int,@to int, @final_row int
select @from=20, @to=25, @final_row = @to - @from

exec ('select top '+ @final_row + ' orderid,freight from orders
where orderid not in
(select top ' + @from + 'orderid from orders o
order by freight desc)
order by freight desc')

--
Vishal Parkar
vgparkar@yahoo.co.in