CODEWITHSUNDEEP
mysql
Williamsson
Williamsson

Reputation: 35

SELECT COUNT messes with ORDER BY

I have a problem I really don't understand. The query

SELECT
    f.*,
    ftv.content,
    ftv.updated,
    COUNT(ftv.file_number) AS versions
FROM 
    files as f
INNER JOIN 
    files_text_versions AS ftv
ON 
    ftv.file_number = f.file_number
WHERE 
    f.file_number = '%s'
ORDER BY 
    ftv.updated DESC
LIMIT 1

totally ignores the ORDER BY statement, but if I remove the

COUNT(ftv.file_number) AS versions

the query runs fine and orders as it should.

Why is that, and how do I fix it?

SQL fiddle with same problem but only one table:

http://sqlfiddle.com/#!2/c8f124/2

Upvotes: 0

Views: 89

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Your count(*) turns the query into an aggregation query. Without a group by, it just returns one row. Although in other databases you would get an error, MySQL allows this syntax.

You can fix this by adding a group by. However, you then have a problem with the other two columns from ftv. You can get the values you want with clever aggregation:

SELECT f.*,
       substring_index(group_concat(ftv.content order by ftv.updated desc), ',', 1) as content,
       MAX(ftv.updated) as updated,
       COUNT(ftv.file_number) AS versions
FROM files f INNER JOIN 
     files_text_versions AS ftv
     ON ftv.file_number = f.file_number
WHERE f.file_number = '%s'
GROUP BY t.file_number
ORDER BY updated DESC
LIMIT 1;

Upvotes: 3

Related Questions