Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Question Unanswered: Selecting a set amount of records

    Hi there,

    i am coding an e-commerce website and i want to do manual recordset paging so that i can format the display properly.

    i want to do recordset paging in sets of 50 or 100 records depending on what option the customer selects on the page

    if i have 2000 products in a category how would i select page 4??

    page 4 being from row 200 in the SQL Database????

    thanks in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database are you using? and how do you number your rows?

    you're not going to like the ANSI SQL solution (the forum you posted in)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by r937 View Post
    you're not going to like the ANSI SQL solution (the forum you posted in)
    It's actually not that ugly:

    Code:
    SELECT col1, col2, col3
    FROM ( 
       SELECT col1, col2, col3, 
              row_number() over (order by some_col) as rn
       from the_table
    ) t
    WHERE rn between 200 and 300
    ORDER BY rn

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat View Post
    It's actually not that ugly:
    of course not

    however, the likelihood that buddy's dbms (my guess: mysql) supports row_number is slim
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2012
    Posts
    3
    hey guys thanks for the reply

    its a hosting package with fasthosts who use SQL Server v10.

    the table has a field called dbid which is a integer and identity column.

    after testing the database coding for adding images to the records it started from 14 and goes upto 114 at the moment, but the client only spent a couple hours adding products last nite so there are many many more to be added. i want to do paging of the products that are selected so that they can display 50 or 100 on a page at a time.

    The SQL so far is Select * From TinksProducts as i am not sure how to execute a select statement that defines the starting point of the selection

  6. #6
    Join Date
    Mar 2012
    Posts
    3
    i tried the select statement u posted and i get this 'dbid' is not a recognized built-in function name.

    Dim cmd As New SqlClient.SqlCommand("SELECT col1, col2, col3" & vbCrLf & _
    "FROM ( " & vbCrLf & _
    "SELECT col1, col2, col3, " & vbCrLf & _
    "dbid() over (order by dbid) as rn" & vbCrLf & _
    "from TinksProducts" & vbCrLf & _
    "WHERE rn between " & startval & " and " & endval & vbCrLf & _
    "ORDER BY rn)")

    thats how i send the command to the server to get the results.

    where am i going wrong to select the records between the start value and end value?

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I did not use a function called dbid()

    Please look again at my statement.

    And does your table really have columns named col1, col2 and col3?
    Then you should immediately dump your data model and re-think it.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by r937 View Post
    however, the likelihood that buddy's dbms (my guess: mysql) supports row_number is slim
    Which is more or less the last one (and SQLite) that does not (at least partially) support windowing functions (and looking at the rate MySQL is implementing modern SQL features, I gues that won't happen before V9 or V10)

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat View Post
    Which is more or less the last one (and SQLite) that does not (at least partially) support windowing functions
    ms access doesn't either, as far as i know

    and of course these forums are frequented by many people on older versions of database systems, like ms sql 2000, that don't support them either

    which is why i always ask posters in the ANSI SQL forum what they're running

    90% of the threads in this forum are from people who really really ~don't~ want an ANSI SQL solution

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by r937 View Post
    ms access doesn't either, as far as i know

    and of course these forums are frequented by many people on older versions of database systems, like ms sql 2000, that don't support them either
    But why restrict ourselves to the capabilities of outdated or limited DBMS? If we don't show people what the current state of the art in SQL is, then everybody will assume that the limits imposed by a few DBMS that don't keep up with modern SQL is everything that can be done.

    There are so many DBMS supporting windowing functions nowadays (Oracle, PostgreSQL, DB2, SQL Server, Terradata, Sybase, Greeplumn, Ingres, Apache Derby partially, Firebird 3.0 will have it) that this feature can be considered "mainstream" in my opinion (just like sub-selects or derived tables)

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat View Post
    ... this feature can be considered "mainstream" in my opinion (just like sub-selects or derived tables)
    i totally agree

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    SELECT col1, col2, col3,
    from the_table
    ORDER BY colx
    OFFSET 200 ROWS
    FETCH NEXT 100 ROWS ONLY


    ANSI/ISO SQL-2011's Feature 857 Top-level <fetch first clause> in <query
    expression>
    , and Feature F861 Top-level <result offset clause> in
    <query expression>
    .


    <result offset clause> ::=
    OFFSET <offset row count> { ROW | ROWS }
    <fetch first clause> ::=
    FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }
    <fetch first quantity> ::=
    <fetch first row count>
    | <fetch first percentage>
    <offset row count> ::=
    <simple value specification>
    <fetch first row count> ::=
    <simple value specification>
    <fetch first percentage> ::=
    <simple value specification> PERCENT

Posting Permissions

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