2

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.

  • 1
    Please provide sample table DDL (CREATE TABLE My_Table....), sample data in the form of (INSERT INTO My_Table....), sample data and desired result. Help us to help you. – Vérace Nov 20 '15 at 21:29
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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