Select the latest changed row in a table 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?
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
SELECT t.*
FROM test AS t
  JOIN
  ( 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
SELECT t1.*
FROM test AS t1
  LEFT JOIN test AS t2 
    ON  t1.name = t2.name
    AND t1.g.changedate &lt; t2.changedate
WHERE t2.name IS NULL ;

-- Query 2b
SELECT t1.*
FROM test AS t1
WHERE NOT EXISTS
      ( SELECT *
        FROM test AS t2 
        WHERE t1.name = t2.name
          AND t1.changedate &lt; t2.changedate
      ) ;