Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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 =
    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 =
    AND t1.g.changedate < t2.changedate

-- Query 2b
FROM test AS t1
      ( SELECT *
        FROM test AS t2 
        WHERE =
          AND t1.changedate < t2.changedate
      ) ;
share|improve this answer
really nice tips. My +1. – Praveen Prasannan Sep 18 '13 at 4:08
(SELECT id, changedate, name FROM test 
  ORDER BY changedate DESC)
as output_name GROUP BY name;
share|improve this answer
cool that works. so TEST should be AS output_descriptor – rubo77 Sep 17 '13 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." – ypercube Sep 17 '13 at 22:40
Link: MySQL Extensions to GROUP BY – ypercube Sep 17 '13 at 22:42
@ypercube: so can you post a better alternative answer? – rubo77 Sep 17 '13 at 23:01
@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.) – ypercube Jan 8 '14 at 15:57

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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