Monday, November 12, 2018

How to retrieve the most special record in each group in MySQL

Using left join:
SELECT a.*
FROM table a LEFT JOIN table b
 ON (a.column1 = b.column1 AND a.column2 < b.column2)
WHERE b.column2 IS NULL;
Using sub-query:
select
    a.*
from
    table a
    inner join 
        (select column1, max(column2) as max_column2 from table group by column1) as b on
        a.column2 = b.max_column2
Which one is more efficient depends on the nature of your data. You can try both and compare them. Check the output of "explain".

When you apply the GROUP BY to only one column, there is no guarantee that the values in the other columns will be consistently correct. Although most of the time the other columns will return the first row, there is no document for it and it is not guarantee.