MySQL LIMIT doesn't work / scope

Question

I've got that rank query with left join. Trying to limit results to 3 rows, but getting all results instead.

SELECT
    CONCAT(users.name, users.surname) n,
    total_time v,
    1+(SELECT count(*) from app a WHERE a.total_time > b.total_time) r 
FROM app b
LEFT JOIN users
    ON b.user_id = users.user_id
ORDER by total_time DESC
LIMIT 3

I know I could wrap it into another select and then limit it (this is how it was and it used to work) but the change is needed since this is part of more complex query.

Query does react on DESC to ASC change, but on LIMIT val doesn't. If I change b.user_id to app.user_id - It throws #1054 - Unknown column 'app.user_id' in 'on clause'

How do I limit this to get 3 rows at the most? Where is the scope of the LIMIT I used?

Any chance of a [SQLFiddle](http://sqlfiddle.com/)? – Phil Feb 20 '15 at 03:35 — Phil, Feb 20 '15 at 03:35
Try this ORDER by a.total_time DESC – Yogesh Patel Feb 20 '15 at 03:36 — Yogesh Patel, Feb 20 '15 at 03:36
Error #1054 because app table has already an ALIAS – Rigel1121 Feb 20 '15 at 03:39 — Rigel1121, Feb 20 '15 at 03:39

score 1 · Answer 1 · answered Feb 20 '15 at 03:36

The query you've shown will return at most three rows. The "scope" of the LIMIT clause is with the outermost SELECT in the query you've shown.

We could setup a SQL fiddle to demonstrate, but you can just as easily test it. You can't get that query to return more than three rows.

MySQL LIMIT doesn't work / scope

1 Answers1