CODEWITHSUNDEEP
mysqlsql
Grzegorz
Grzegorz

Reputation: 3608

Why does MySQL query not return newest row

I got specific query:

SELECT * 
FROM stats 
WHERE mod_name = 'module' 
GROUP BY domain 
ORDER BY addition_date DESC

I want to retrive, the newest value for every domain. I know there is a domain x.com value with date 2014-02-19.

However, this query returns me row with date: 2014-01-06

That's quite simple query... why it does not take group by domains and take only newest value? Am I missing something?

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

The order by takes place after the group by. That is why your query does not work. Here is a way to get what you want:

SELECT s.*
FROM stats s
WHERE mod_name = 'module' and
      not exists (select 1
                  from stats s2
                  where s2.mod_name = s.mod_name and
                        s2.addition_date > s.addition_date
                 )
ORDER BY addition_date DESC;

To get the best performance, create an index on stats(mod_name, addition_date).

Upvotes: 2

Related Questions