Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2004
    Location
    Dundee, Scotland
    Posts
    45

    Question Unanswered: Selecting a range of records in a SQL Statement

    I want to run a query that returns say 100 records...but I only want to return first 10 for first page on a web page, then on page 2 the webpage will return rows 11 to 20 of the same SQL statement...page 3 returns rows 21 to 30 rows etc....(eg. like Google or bulletin boards, browsing auctions in ebay etc.).

    I could probably get my application logic to handle this (ASP.net), for instance I could possibly get a datareader to skip the first 10, output the next 10 then stop for page 2 (records between 11 amnd 20) but is there way to do this in SQL Server at the database level using an SQL Statement?

    I can use TOP 10 to get the first set of records for the first page eg.

    SELECT TOP 10* FROM Suppliers

    ...but how do I get between 11 and 20, 21 and 30 and so on?

    I've already mentioned I could handle this in my application logic, but then each time the same SQL Statement is fetching all 100 records, even though the web page will only display a certain range. I'm building an intranet website that can potentially run queries that return 100'000s records, even though initially only top 20 or so records are display, each page they subsequently go to will rerun that same query that returns all 100'000 records. So handling this as part of the query would be better for performancr I reckon.

    Someone gave me the syntax..

    SELECT *
    FROM employees
    LIMIT 10,10;


    ..but it didn't seem to work.

    Thanks for any help!!
    Sunday Ironfoot

    SQL Server 2000 n00b

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You will need to pass in the last SortKey and PrimaryKey values returned by the previous call, or Nulls if this is the first call to the procedure. @PageSize is the number of records you want to return.

    Code:
    create procedure PagedRecordset(@PageSize integer, @LastSortKeyValue, @LastPrimaryKeyValue)
    as
    begin
    select	YourTable.SortKey,
    	YourTable.PrimaryKey,
    	.
    	.
    	[ColumnN]
    from	YourTable
    	inner join YourTable RowCountTable
    		on (YourTable.SortKey < RowCountTable.SortKey
    		    or (YourTable.SortKey = RowCountTable.SortKey
    		        and YourTable.PrimaryKey < RowCountTable.PrimaryKey))
    		and RowCountTable.SortKey < @LastSortKeyValue
    		    or (RowCount.SortKey = @LastSortKeyValue
    			and RowCount.PrimaryKey < @LastPrimaryKeyValue)
    		or @LastSortKeyValue is null
    where	YourTable.SortKey < @LastSortKeyValue
    	or (YourTable.SortKey = @LastSortKeyValue
    		and YourTable.PrimaryKey < @LastPrimaryKeyValue)
    	or @LastSortKeyValue is null
    having	count(*) < @PageSize
    end
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    Dundee, Scotland
    Posts
    45
    I think I understand what you're getting at, thanks! So would retrieving the last primary key value for the top 10 records for the first page, then using that key to start the count from that record onwards eg. (in pseudo code)

    If Page = 1 Then
    "SELECT TOP 10* FROM Suppliers"
    Else
    "SELECT TOP 10* FROM Suppliers WHERE SupplierID > " & Request.QueryString("lastPK")
    End If

    Then the URL link that goes to the next page passes a querysting that is the last Primary value for the current record set. I wonder if something like that would work?
    Sunday Ironfoot

    SQL Server 2000 n00b

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Either I am missing something or both of you are forgetting ORDER BY clause.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your simpler query (using TOP 10) will work fine as long as you are satisfied with always returning 10 records. The TOP property will not take variable parameters, so you'd have to muck around in dynamic SQL if you want more flexibility as to the size of the recordset returned.

    ORDER BY is not required in my example, but you will need to include it in yours since you are not numbering the result set.
    Last edited by blindman; 02-12-05 at 22:10.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try using something like this, but please replace SELECT * with an enumerated list of column names!

    Code:
    SELECT	TOP 10 *
    FROM	Suppliers
    WHERE	SupplierID > " & Request.QueryString("lastPK")
    	or lastPK is null
    order by	SupplierID
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2004
    Location
    Dundee, Scotland
    Posts
    45
    Quote Originally Posted by rdjabarov
    Either I am missing something or both of you are forgetting ORDER BY clause.

    It's true, since if I wanted to order by a different column then the identity column may end up being in a random order and my "find top 10 rows from identity column past certain primary key number" idea won't work, oh well

    But I Have find this page at MSDN:

    http://msdn.microsoft.com/library/de...nethowto05.asp

    But as an aside my friend reckons MySQL supports something called LIMIT X,Y that lets you select a certain range of rows from within the SQL Statement, none of this writing complex Stored Procedure nonsense. Why doesn't SQL Server 2000 support this, paging through records seems like a fairly common task one would want to perform in an application. I hope they introduce the LIMIT syntax in SQL Server 2005 or I might be tempted to switch over to MySQL!
    Sunday Ironfoot

    SQL Server 2000 n00b

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    switching over to mysql from sql server will also save you some license fees, and if you ever need vendor technical support, you'll see the difference

    limit m,n in mysql requires that the entire result set be queried by the engine, even though only N rows are returned to the client
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    Dundee, Scotland
    Posts
    45
    Quote Originally Posted by r937
    limit m,n in mysql requires that the entire result set be queried by the engine, even though only N rows are returned to the client
    Dam, that throws that idea out of the window then, kinda looks like I'll be sticking with SQL Server

    I'm after querying just rows I need rather than all of them to speed up query performance, otherwise I'd just let application logic handle all paging from SQL Server returning the entire record set. I think I found a way.

    Originally, from suggestions above, I was gonna select TOP number of records I didn't need, find the primary key identity number for the very last record and rerun the select but TOP 10 from the last PK number in the records I dont want, err..if that makes sense . But as already established that won't work if you're ordering by a different column, so what about rather than using last primary key identifier, use last column data for column you're ordering by then get TOP 10 from there. eg. (in psuedo code)

    (If ordering by PK Identifier)
    SELECT TOP 10* FROM Suppliers WHERE SupplierID > "lastPKNum"
    (where "lastPKNum" is the last PK number from the previous page)

    (To order by a different column)
    SELECT TOP 10* FROM Suppliers WHERE ContactName > 'John Smith' ORDER BY ContactName
    (where 'John Smith' is the last ContactName from the previous page)

    I've tried it in Northwind database (using a real ContactName of course) and it seems to work. Only thing is application logic to build the SELECT statement is gonna be horrendously complicated, well I'll give it a go anyway.
    Sunday Ironfoot

    SQL Server 2000 n00b

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Can't say too much here, but Yukon WILL introduce something that will replace the need to write complex code to paginate the results. And that "something" is VERY elegant
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...in respect to ORDER BY...
    If you are relying on IDENTITY column being a PK to order your resultset, then this may take you where you don't want to be. As per MS, order of a result set without ORDER BY is NOT guaranteed to conincide with the order of the clustered index/PK, especially on SMP systems.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    WRT Yukon, can we assume elegant is a superset of scaleable?
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    excellent, maxA

    another database guy with a sense of humour

    be sure to stop in at the dbforums bar and say hello

    great for trading horror stories and workplace gossip
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2004
    Location
    Dundee, Scotland
    Posts
    45
    Quote Originally Posted by r937
    excellent, maxA

    another database guy with a sense of humour
    I don't get it!
    Sunday Ironfoot

    SQL Server 2000 n00b

  15. #15
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    I'll try to explain. However, you should research what Microsoft is saying about Yukon (white papers, Beta places, etc.) to get the full story as those who are using Yukon have to be careful what they say to avoid breaking any agreement they have with Microsoft.

    Assuming that Microsoft offers the ability to page results in Yukon, it is only deemed elegant by the sorts like me if it also scales well. Thus, elegance must contain scalability e.g. be a superset.

    Or, I will ignore it like I ignore client-side cursors. Some consider client-side cursors (and .NET datareaders) as elegant. I don't.

    Abstraction that leads to problems is aka distraction.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •