I need to create a "Select" statement for the "TOP" x to y number of the
records in the query. I know I can put in like "TOP 10" and it will return
the top 10 number of the record. However I want to be able to return a range
say Top 20-30 number of the records or Top 30-50 number of the records.
Thanks.

Pail
use Northwind
go
select orderid,(select count(*) from orders o where
o.orderid<=orders.orderid) as rnk
from orders
where (select count(*) from orders o where
o.orderid<=orders.orderid) between 10 and 20


--------------------------------
create table #w
(
col1 int identity,
col2 varchar(2)
)
insert into #w values ('y')------populate with data
select * from #w

select col1 from
         (select top 10 col1
                      from
                       (select top 20 col1 from #w order by col1 ASc)AS F
                        order by col1 DESC) as t1
order by col1 asc



BTW , Aaron has a great arsticle on his web site www.aspfaq.com  (search for
paging)





Show quoteHide quote

The below query lists the records from 10 to 15 from a table. The logic is we
need to fetch the first 15 records from the table then take the first 6
records within it in the DESCending order. Hope the logic is pretty straight
forward!!

Select * From
    (
    Select TOP 6 * From -- (A1)
        (
            Select TOP 15 * from employeeTable order by Sno --- (A2)
        )
        as D1 ORDER BY Sno DESC
    )
    as D2 ORDER BY Sno

I have made a post on this couple of years back here
http://vadivel.blogspot.com/2003/12/listing-records-from-10-to-15-for-ex.html

Hope this helps!

Best Regards
Vadivel

Blog: http://vadivel.blogspot.com
SQL Articles:
http://vadivel.blogspot.com/2005/11/list-of-my-sql-articles-tips.html

Show quoteHide quote

Still struggling w/ LAST transaction date!!!
DISTINCT MonthName for a lot of dates....
Dynamic Stored Procedure
Dynamic WHERE & Dates
Sum() And Nulls