selecting complete rows grouped by one column based on the maximum of another

Question

I'm trying to find a query selecting all columns of my table, where the result set contains only distinct values of a certain column. The row being selected for a specific distinct value should be the one with the maximum value of another column (within the group of rows having that value for the former column).

I tried

SELECT column1, column2, MAX(column3) FROM table GROUP BY column1;

which doesn't work because in the results column2 and MAX(column3) are not from the same row. How is this achieved in mysql?

Example

For a table like

CREATE TABLE table (column1 VARCHAR(10), column2 VARCHAR(10), column3 INT);

and data like

INSERT INTO table VALUES ("a", "x", 1);
INSERT INTO table VALUES ("a", "y", 2);
INSERT INTO table VALUES ("a", "z", 3);
INSERT INTO table VALUES ("b", "x", 1);
INSERT INTO table VALUES ("b", "y", 2);

I want to get the rows ("a", "z", 3) and ("b", "y", 2). With the above query I get ("a", "x", 3) and ("b", "x", 2) i.e. MAX(column3) and the rest of the result do not come from the same original row in my data. I see why that is not possible with aggregation functions in general but I can't think of another simple way to achieve it.

Answer 1

general answer

SELECT t1.* from [table] t1 
JOIN (SELECT column1, MAX(column3) as max_c3 from [table] group by column1) t2 
ON t1.column1 = t2.column1 AND t1.column3 = t2.max_c3

how is it work -

SELECT column1, MAX(column3) as max_c3 from [table] group by column1) t2

prepare all rows with correct column1 and MAX(column3)

JOIN ON t1.column1 = t2.column1 AND t1.column3 = t2.max_c3 

correctly select other columns corrected for result,

if table have more than 1 row with same MAX(column3) - query return 1 row for each MAX(column3) result.

Example data:

id; column1; column2; column3;column4
1   10  50  1   1
2   10  60  2   2
3   12  50  3   3
4   12  60  4   4
5   11  70  5   5
6   10  99  6   6
7   11  55  7   7
8   10  60  6   8

result:

id; column1; column2; column3;column4
8   10  60  6   8   10  6
6   10  99  6   6   10  6
7   11  55  7   7   11  7
4   12  60  4   4   12  4

if delete from table row with id=8 (remove duplicates for max(column3)), result will be:

id; column1; column2; column3;column4
6   10  99  6   6   10  6
7   11  55  7   7   11  7
4   12  60  4   4   12  4