How can i filter on row number? SQL Server

paddycobbett
paddycobbett used Ask the Experts™
on
I need to be able to filter on row number, but am confused by the row_number() function which i don't think suits my purporse.

I essentially need to be able to request that i only receive rows 20 to 30 from the result. I know it is possible to request the TOP x number of rows, so there must be some similar support for this. I'm doing this to allow a user to "page" through results based on their parameters, so only need the results that reflect the result page they requested. Can someone add a clause to the attached code to return just the rows from 20 to 30? Or else suggest how they would go about doing this. I'd rather not do this filtering on the programming language level for obvious performance reasons.

Thanks for any comments/suggestions :)


SELECT entry FROM tag_system
WHERE filter='x'
AND ..

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
it can help, but you need to specify a ORDER for rownumber
SELECT * FROM (
  SELECT entry   
       , ROW_NUMBER() OVER ( ORDER BY entry ) r
   FROM tag_system
  WHERE filter='x'
    AND ..
 ) sq
WHERE sq.r between 20 and 30

Open in new window

Author

Commented:
Spot on. Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial