MySQL "limit" query

[markh]

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

[randem]

SELECT * FROM REQUESTINFO rq, REPLY r 
WHERE rq.id = r.requestid ORDER BY rq.date DESC LIMIT 10;

[markh]

...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

[r937]

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?

[markh]

I'm getting the lot - all 3000-odd of them

[r937]

no idea

anyone?

[Goof]

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

[markh]

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?

[r937]

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

[MattR]

What version of MySQL are you using? Maybe there's a bug in the version you're at.

[markh]

MySQL version is 3.23.42

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

Mark

[r937]

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

[MattR]

Sounds like a weird bug. Your version is old (Sep 8 2001), try getting the latest version.