I have a Mysql table test with the fields id, changedate, name.

some names have multiple changedates.

I can select all rows with

SELECT id, changedate, name FROM `test` ORDER BY changedate;

How can I select only the newest rows of that table for each name?

2 Answers 2


If you don't need the id (or any other column, not included in the question) but only the name and the newest changedate, then you only need a simple GROUP BY query - and an index on (name, changedate) for efficiency:

SELECT name, MAX(changedate) AS changedate
FROM test 
GROUP BY name ;

If you need more columns, one way to solve is to put the above query in a derived table and JOIN to the original table:

-- Query 1
FROM test AS t
  ( SELECT name, MAX(changedate) AS changedate
    FROM test 
    GROUP BY name
  ) AS g
    ON  g.name = t.name
    AND g.changedate = t.changedate ;

You could also use a LEFT JOIN or a NOT EXISTS solution:

-- Query 2a
FROM test AS t1
  LEFT JOIN test AS t2 
    ON  t1.name = t2.name
    AND t1.g.changedate < t2.changedate
WHERE t2.name IS NULL ;

-- Query 2b
FROM test AS t1
      ( SELECT *
        FROM test AS t2 
        WHERE t1.name = t2.name
          AND t1.changedate < t2.changedate
      ) ;
(SELECT id, changedate, name FROM test 
  ORDER BY changedate DESC)
as output_name GROUP BY name;
  • cool that works. so TEST should be AS output_descriptor
    – rubo77
    Sep 17, 2013 at 16:48
  • Don't do this. MySQL documentation clearly states: "MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate." Sep 17, 2013 at 22:40
  • Link: MySQL Extensions to GROUP BY Sep 17, 2013 at 22:42
  • @ypercube: so can you post a better alternative answer?
    – rubo77
    Sep 17, 2013 at 23:01
  • 1
    @rubo77 No, I'm right anyway. The internal ORDER BY can be removed by an optimizer as redundant and then the query will not work as you expect (try it in the latest, 5.6 version.) Jan 8, 2014 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.