SitePoint Sponsor |
|
User Tag List
Results 1 to 13 of 13
Thread: MySQL "limit" query
-
Jul 16, 2002, 17:22 #1
- 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
-
Jul 16, 2002, 20:13 #2
- 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!
-
Jul 16, 2002, 20:42 #3
- 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
-
Jul 16, 2002, 21:33 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 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?
-
Jul 16, 2002, 23:00 #5
- Join Date
- Jul 2002
- Posts
- 5
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I'm getting the lot - all 3000-odd of them
-
Jul 17, 2002, 12:47 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
weird
no idea
anyone?
-
Jul 17, 2002, 13:07 #7
- 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
-
Jul 18, 2002, 04:26 #8
- 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?
-
Jul 18, 2002, 06:21 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 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
-
Jul 18, 2002, 06:39 #10
- 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.
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Jul 18, 2002, 16:38 #11
- 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
-
Jul 18, 2002, 19:10 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 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
-
Jul 18, 2002, 19:42 #13
- 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.
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
Bookmarks