return a subset from a query

Question

return a subset from a query

pratapkrg

SSC Enthusiast

Points: 157
More actions
November 21, 2003 at 6:42 am

#106761

I have query which returns 25,000 rows which are displayed in the application by paging. each time it shows 50 records in the application. user can view other records by navigating using pages.
this query returns data from 5 tables(all of them have more than 1,00,000 records). i am using temporary table in this query for paging. is there any way i can retrive subset of records from the query say from 100th record to 150 record with out using temp table.
thanks in advance
Pratap

Viewing 6 posts - 1 through 5 (of 5 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Well, apart from the fact that there isn't such a thing as the 150th row, you might take a look at http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=17248 or http://www.planet-source-code.com/vb/default.asp?lngCId=633&lngWId=5 or http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=13087

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jcraddock Default port Points: 1498 More actions · Answer 2

I do something similar, but my largest recordset is about 15000 rows. I don't know how long your query takes, but what I do is return the TOP (pagesize*pagenumber) records and then go to the pagesize*(pagenumber -1) record and display to the end of the record set.

It sounds whacky, but it works and is quite fast in our situation.

So, for the 8th page of a recordset with a pagesize of 50, I would

Select Top 400 Table.*

then I use a moveto function to goto the 351st record and display from there to the end of the result set. This is an ASP application.

bobsterboy SSCommitted Points: 1549 More actions · Answer 3

I've always used the > clause and a unique index to page through my data. You store the ID on the page and pass that ID to a stored proc which grabs top n records > ID. If you're going to previous, you grab top n records < id sorted descending. Granted, this doesn't give you page count, goto, etc., but you don't always want that. One advantage to this method is you're only getting 50 records, so your server isn't taxed and you're not using much bandwidth. If you're using a clustered index, you'll gain performance for nexts, but resorts for previous will be slower.

David Burrows SSC Guru Points: 65142 More actions · Answer 4

If you are using an ADO recordset it has paging abilities.

Far away is close at hand in the images of elsewhere.
Anon.

Santanu Sen SSC Enthusiast Points: 147 More actions · Answer 5

Check out this article "Using Inline UDFs for Precise Paging" at http://www.15seconds.com/issue/030113.htm