SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: LIMIT question

  1. #1
    SitePoint Addict brain's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    LIMIT question

    How must I query if I want to display all the records excerpt the first 10. So something like:
    WHERE active = '1' LIMIT 9, (and the rest)

  2. #2
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi,
    limit clause can skip given number of rows if you give it 2 numbers.
    e.g. limit 2,5 skips 2 first rows and show rows 3 to 8 (next 5 rows). so if you wanna skip first n rows using LIMIT clause,you should know number of all rows.

    Chagh

  3. #3
    SitePoint Addict brain's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm, I know I can use two statements, the first LIMIT 0,9 and then with the second I can use the ID from the first: where ID > ID extracted from the first.
    But I wanted to know if I can manage it within only one statement.

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Using the ID can be risking for counting rows since if a row is deleted the newest id doesn't reflect the total number of rows.

    In order to get the number of rows you can use SELECT count(*) FROM `table`

    Tt was a long shot, but I tried this:

    SELECT * FROM `test` LIMIT 2, (SELECT count(*)-2 FROM `test`)

    BUT IT DOES NOT WORK

  5. #5
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    SELECT * FROM test 
    where pkColumn not in 
    (select pkColumn
      from test
     order by pkColumn 
     limit 0,2)
    Using limit without an order by clause does not make any sense. You should not assume that data are stored in some specific order

    Or you could just give some sufficiently high number. (When would you be interested in displaying more than a hundred rows on a page anyway?)

    Code:
    select *
      from test
     order by pkColumn 
     limit 2,100


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
  •