Limiting returned rows in a query?????

Question

Post reply

Limiting returned rows in a query?????

Silverado

Valued Member

Points: 70
More actions
July 20, 2007 at 8:00 am

#196985

I am trying to create a dataset for a report in "Reporting Services." I need to extract a range of rows from my query. I used "SET ROWCOUNT" to get the first 75 rows, now I need to get the next 75. Does anyone know how to return a range like "ROWS (75-150)"?????

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

WesleyB Ten Centuries Points: 1307 More actions · Answer 1

That is something you will have to take care of yourself.

Using that last offset of the primary key and the TOP statement can help you out.

Jeff Van Dyke SSC Enthusiast Points: 138 More actions · Answer 2

I've come across this issue quite often and have developed a workaround (*hack*) by using a stored procedure to load the data into a temp table that has the first column defined as an identity column that I like to call ROWCOUNT. Once you have all of the data loaded into the temp table in the order you want, you can get ROWCOUNT BETWEEN 1 AND 75, ROWCOUNT BETWEEN 76 AND 150, etc.

Hope that helps.

Service may vary according to my mood and your attitude.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

Good workaround, but a bit slow and resource intensive if you have millions of rows...

Something like the following works very fast... yeah, it does use dynamic SQL but that's only because I'm still using SQL Server 2000...

The following code will return Page 1 in about 16 milliseconds and Page 12,000 (1.2 million rows later) in about 400-500 milliseconds...

declare @start datetime

set @start = getdate()

--===== Declare the local variables

DECLARE @PageSize INT --How many rows to appear per page

DECLARE @PageNum INT --What page number to appear

DECLARE @Skip INT --Working variable for where to start for page

DECLARE @sql VARCHAR(8000) --Holds dynamic SQL

--===== Set the local variables for pagesize and page

-- PageSize and PageNum would be parameters in a stored proc

SET @PageSize = 100

SET @PageNum = 4000

SET @Skip = @PageSize*@PageNum

--===== Create the dynamic SQL statement that will produce the page

SET @sql =

'

SELECT c.*

FROM dbo.Customer c,

(

SELECT TOP '+STR(@PageSize)+ ' *

FROM dbo.Customer WITH (NOLOCK)

WHERE CustID NOT IN (SELECT TOP '+STR(@Skip)+' CustID

FROM dbo.Customer

ORDER BY CustID)

ORDER BY CustID

) d

WHERE c.CustID = d.CustID

'

--===== Produce the page

EXEC (@SQL)

print CONVERT(varchar(30),getdate()-@start,114)

I'm hoping to see someone use the "new" rownumber function for this... that should actually be much faster...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Silverado Valued Member Points: 70 More actions · Answer 4

Thanks for all of your advice. You put me on the right track.

Jeff Van Dyke SSC Enthusiast Points: 138 More actions · Answer 5

Unfortunaly the ROW_NUMBER function is a 'Windowed function' and can only be used in the SELECT and ORDER BY clauses ....

It may still be possible to make that work somehow, but it's past my knowledge.

Service may vary according to my mood and your attitude.

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 6

One option would be to add the number of rows to be displayed in the report as a parameter and then use "TOP" to return only the number of rows requested?

Normally paging/pagination is handled by the report (e.g. reporting services takes all of the rows returned figures out the pagination, and generates a report X number of pages long) so I'm wondering what you're trying to accomplish by short circuiting the process?

Joe

Pinal Dave Old Hand Points: 316 More actions · Answer 7

This link demonstrates the usage of Rank Function for SQL Server 2005 paging technique.

http://blog.sqlauthority.com/2007/06/11/sql-server-2005-t-sql-paging-query-technique-comparison-over-and-row_number-cte-vs-derived-table/

Kindest Regards,

Pinal Dave
sqlauthority.com

icata Hall of Fame Points: 3176 More actions · Answer 8

Check Inside Microsoft SQL Server 2005 T-SQL Querying by Itzik Ben-Gan - there are a few solution for this issue, some for 2000, some for 2005.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 9

Guess you can't actually look into a book on Amazon anymore, huh

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tim Januario SSChasing Mays Points: 609 More actions · Answer 10

Look up Common Table Expressions (CTE) in books online. They give example of how you can do paging by using a CTE. I have seen some things that say that CTE's have efficiency problems (at least with hierarchy work) but I have had no problems, and they really do make it easier to do paging (which is what it sounds like you want to do.

The basic syntax is:

DECLARE @start int, @end int
SELECT @start = 1, @end = 75
BEGIN -- this is important if you have code before the CTE

WITH MY_CTE AS (

SELECT

ROW_NUMBER() OVER (ORDER BY some_column) AS ROWNUMBER,
* -- Don't use * I just don't want to make this long

FROM

SOMETABLE

)

SELECT

* -- again with the *

FROM

MY_CTE

WHERE

ROWNUMBER BETWEEN @start AND @end

END