CODEWITHSUNDEEP
sql-server
RaJesh RiJo
RaJesh RiJo

Reputation: 4400

SQL Server: SELECT TOP 1001 to 1100 Rows

Here is my prob,

I wanted to select particular 100 rows from a table that contains 70,000 rows. I tried with OFFSET, but it takes almost time taken for fetching all 70,000 rows.

I used this query,

select ContentUrl from EDContentMaster order by title desc 
offset 1000 rows 
fetch next 100 rows only

So is there any way to select particular 100 rows(i.e from 1001th row to 1100th row) with less time?

Thanks in Advance.

Upvotes: 0

Views: 686

Answers (3)

Dhaval
Dhaval

Reputation: 2379

You can use ROW_NUMBER in SQL Server versions 2005 to 2008 R2

with cte as 
(
    select row_number() over(order by title desc) as rno, 
           ContentUrl 
    from EDContentMaster
)
select * from cte where rno between 1001 and 1100

Upvotes: 4

Chiragkumar Thakar
Chiragkumar Thakar

Reputation: 3716

Try this, will work for you.

SELECT ContentUrl FROM (
    select ContentUrl,ROW_NUMBER() OVER (ORDER BY title DESC) AS RowNumber  from EDContentMaster 
) AS InnerTable
WHERE RowNumber BETWEEN @StartIndexParameter AND @StartIndexParameter + @Count

Upvotes: 2

Aaron Hernandez
Aaron Hernandez

Reputation: 158

Did you try to use this?

SELECT ContentUrl from EDContentMaster LIMIT 100, 1001;

or

 SELECT ContentUrl from EDContentMaster LIMIT 100 OFFSET 10001;

Upvotes: 0

Related Questions