How to select data top x data after y rows from SQL Server

Question

For example I have a table which contains 10'000 rows. I want to select top 100 rows after top 500th row. How can I do this most efficiently.

Query needed for SQL Server 2008

---

For example i have this query already but i wonder are there any more effective solution

SELECT TOP 100 xx
FROM nn 
WHERE cc NOT IN 
   (SELECT TOP 500 cc
      FROM nn ORDER BY cc ASC)

Solution 1

Tutorial 25: Efficiently Paging Through Large Amounts of Data

with cte as (
 SELECT ...,
  ROW_NUMBER () OVER (ORDER BY ...) as rn
 FROM ...)
SELECT ... FROM cte 
WHERE rn BETWEEN 500 and 600;

Solution 2

SELECT 
  col1,
  col2
FROM (
    SELECT ROW_NUMBER() OVER (
        ORDER BY [t0].someColumn) as ROW_NUMBER,
    col1,
    col2
    FROM [dbo].[someTable] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN 501 and 600
ORDER BY [t1].[ROW_NUMBER]

Solution 3

Selecting TOP 500, then concatenating the TOP 100 to the result set.

Normally, in order to worth doing this, you need to have some criteria on which to base what your need 500 records for, and only 100 for another condition. I assume that these conditions are condition1 for the TOP 500, and condition2 for the TOP 100 you want. Because the conditions differ, that is the reason why the records might not be the same based on TOP 100.

select TOP 500 *
    from MyTable
    where -- condition1 -- Retrieving the first 500 rows meeting condition1
union
    select TOP 100 *
        from MyTable
        where -- condition2 -- Retrieving the first 100 rows meeting condition2
-- The complete result set of the two queries will be combined (UNIONed) into only one result set.

EDIT #1

this is not what i meant. i want to select top 100 rows coming after top 500 th row. so selecting rows 501-600

After your comment, I better understood what you want to achieve. Try this:

WITH Results AS (
    select TOP 600 f.*, ROW_NUMBER() OVER (ORDER BY f.[type]) as RowNumber
        from MyTable f
) select *
    from Results
    where RowNumber between 501 and 600

Does this help?

Comments

[MonsterMMORPG]

this is not what i meant. i want to select top 100 rows coming after top 500 th row. so selecting rows 501-600

[Admin]

downvoted for select statement within select statement. THe inner select statement is extremely inefficeint. IT does an RBAR search, simple-talk.com/sql/t-sql-programming/… If it can be avoidid (most times it can) never do inner selects

[MonsterMMORPG]

why i want is pretty simple. simply paging. for example you have 1000 records. but for per page you show user 100. i dont want to select all 1000 results at once.

[Will Marcouiller]

Then you'll need to select a larger result set and filter it by the ROW_NUMBER

[Mr. Smith]

I'd pose the question: Why? Sometimes it's unavoidable.

[Admin]

@Mr.Smith The link i pasted can explain much better then I. And you are right sometimes it is unavoidable but those times are few and far in between. For example @Remus Rusanu shows using cte (common table expressions) which will work better because it is doing the table scans differently. I recommend reading about RBAR sql expressions.

[adam0101]

@george9170, I've compared @Remus Rusanu's method with my own. The execution plans look identical to me.

[MonsterMMORPG]

can we say this is the most efficient way ?

[MonsterMMORPG]

yes this would work but what Remus Rusanu said seems like more efficient

[adam0101]

@george9170, the link you posted doesn't make it clear for me how my query is that much more inefficient. I could see if I were doing a SELECT within a SELECT clause, but I'm doing an inline query within the FROM clause. Do you have any other resources that explain the RBAR condition? The performance appears to be identical to @Remus's solution.

[Remus Rusanu]

No. This still have to, even in the best case, scan 500 rows to count them so it knows when to start. In the worse case it needs to scan all rows, sort them and then count 500 sorted rows to reach the starting point. Things get worse if you ask for the row 9500 to 10000, and then far worse for rows 999500 to 1000000. The most efficient way is keyset driven: SELECT TOP(500) ... FROM ... WHERE key > @lastPageLastKey, but this require fancy logic in the client (keep track of keys) and does not allow for arbitrary column orders.

[MonsterMMORPG]

i see. but it is quite impossible for me since there are filtering features.