Professional Web Applications Themes

SQL Statement Paging - Microsoft SQL / MS SQL Server

Hi All, I came up with the following sql to do the paging.... SELECT TOP 10 * FROM ORDERS WHERE ORDERID NOT IN (SELECT TOP 20, ORDERID FROM ORDERS) --|Where 10 is my records size per page. but soon I realise that it is not the most efficient way of doing it ... any better ways ?...

  1. #1

    Default SQL Statement Paging

    Hi All,

    I came up with the following sql to do the paging....

    SELECT TOP 10 * FROM ORDERS WHERE ORDERID NOT IN (SELECT TOP 20, ORDERID
    FROM ORDERS)

    --|Where 10 is my records size per page.

    but soon I realise that it is not the most efficient way of doing it ...
    any better ways ?





    Bernard Guest

  2. #2

    Default Re: SQL Statement Paging

    Check out this article:
    http://www.fawcette.com/dotnetmag/2002_10/magazine/columns/architecting/default_pf.asp

    Although it talks about ASP.NET, you can ignore it and look at an
    implementation of the stored procedure that does the paging and the
    algorithm discussed in the same.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Bernard Goh" <com> wrote in message
    news:phx.gbl... 


    SriSamp Guest

  3. #3

    Default Re: SQL Statement Paging

    >> Correct me if I am wrong, but I think you may have misunderstood the
    purpose of the sql. <<

    You're wrong <g> and have misunderstood the purpose of the SQL language.
    We meant it to be a data retrieval language and not an application
    development language. It is based on logic, not physical implementation.
     [/ref]
    maximum records [sic] of 10 (page size) <<

    First of all, there are no "records" in SQL; a row is not a record.
    HUGE differences! Confusing these concepts leads to blurring the client
    and server sides of the application. You are writing a sequential file
    system in SQL.

    Page size? That is not a LOGICAL term; it is particular PHYSICAL
    implementation for a particular piece of hardware. Why isn't your page
    size 14 or 27 or anything else?
     [/ref]
    query (e.g. 100,000 records [sic]), then have it do the processing and
    finally show the 10 records [sic] I want out of the huge recordset
    [sic]. <<

    Physical counts again! You write a query that returns *all* the rows
    that match your logical selection criteria. You could have zero rows
    that match; you could have one row that matches; could have (n) rows
    that match. If there is a logical ceriteria that assures that that only
    10 rows will match, then put that criteria in the query.

    Otherwise, you are not getting the right answer.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Paging
    By Nick K. in forum ASP.NET Data Grid Control
    Replies: 18
    Last Post: June 17th, 02:57 PM
  2. Help with paging
    By quovadimus02 in forum Macromedia ColdFusion
    Replies: 0
    Last Post: May 2nd, 08:54 PM
  3. Paging
    By Jose in forum AIX
    Replies: 2
    Last Post: October 10th, 02:55 PM
  4. Problem with paging
    By Charles Langlois in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: September 23rd, 09:17 PM
  5. AIX & Paging
    By Krishna in forum AIX
    Replies: 7
    Last Post: July 28th, 04:34 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139