Take the 2-minute tour ×
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
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 < 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 < t2.changedate
      ) ;
share|improve this answer
    
really nice tips. My +1. –  Praveen Prasannan Sep 18 '13 at 4:08
SELECT * FROM 
(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
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.) –  ypercube Jan 8 at 15:57

Your Answer

 
discard

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.