SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL "limit" query

    Hi,

    I am using this query in my code:

    select * from REQUESTINFO rq, REPLY r where rq.id = r.requestid order by rq.date desc limit 0, 10

    Unfortunately it does not limit the records to the first 10. Any idea what I am doing wrong? Should the 'limit' come before the 'order'? Am I just plain dumb?

    Cheers
    Mark H

  2. #2
    morphine for a wooden leg randem's Avatar
    Join Date
    Jun 2002
    Location
    .chicago.il.us
    Posts
    957
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT * FROM REQUESTINFO rq, REPLY r 
    WHERE rq.id = r.requestid ORDER BY rq.date DESC LIMIT 10;
    ----Adopt-a-Sig----
    Your message here!

  3. #3
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thanx,...

    ...but I have also tried this variant. I need to return an arbitrary y records starting at record x, hence the 'limit x,y' syntax.

    Mark H

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    limit does come after order by

    if not the first 10, then which records are you getting? all of them? more than 10? the wrong 10?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm getting the lot - all 3000-odd of them

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)

    weird

    no idea

    anyone?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know squat about MySQL, but MySQL.com seems to have a lot of support-type stuff on their site. Maybe you want to ask there. Also, does it make any difference if you take out the space after the comma? Maybe the engine is just getting confused...

    :shrug:
    Goof
    Nathan Rutman
    A slightly offbeat creative.

  8. #8
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Still not fixed...

    I tried the following combinations tonight:

    limit 10 - returned 3k+ rows
    limit 0,10 - ditto
    limit 0, 10 - ditto
    limit 0 , 10 - ditto
    limit 0 - ditto
    limit 10,10 - ditto

    Talk about frustrated! Would the position of the 'desc' be correct?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, "desc" is in the right place

    are the rows, in fact, being properly sorted in descending sequence by date?

    there's gotta be some reason for it to totally ignore your LIMIT parameter
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What version of MySQL are you using? Maybe there's a bug in the version you're at.

  11. #11
    SitePoint Member
    Join Date
    Jul 2002
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MySQL version is 3.23.42

    The records are returned ordered by date. Does this version of MySQL support 'limit'???

    Mark

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i don't know whether 3.23.42 does or not

    LIMIT works fine in 3.23.47 though -- i just tested it, with DESC too

  13. #13
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds like a weird bug. Your version is old (Sep 8 2001), try getting the latest version.


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
  •