Mysql – How to remove duplicates in col1 and get group MAX in col2? / thesql

group byMySQLselect

Given the following table:

col1 Col2
A    1
B    1
C    1
B    2
B    3
A    0
C    5

how do I write a SELECT query (I just assumed has to be SELECT) that returns the unique items of col1 and the max values of col2, like this:

A   1
B   3
C   5

Best Answer

SELECT col1, MAX(col2) FROM mytable GROUP BY col1

Related Solutions

Mysql – How to get MAX and MIN in GROUP BY query

Would you believe...

SELECT col1, MIN(col2), MAX(col2), MIN(col3), MAX(col3)
  FROM table1
 GROUP BY col1;

...?

each row includes the first value of col2 and col3 for each unique value of col.

That assertion is not exactly true. That may be what you're seeing, but do not assume this to be meaningful and do not write code based on this observation. Your original query would not be valid at all were not for a MySQL extension that departs from standard SQL handling of GROUP BY.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause.

...which is what you did in your initial query, col2 and col3 are not nonaggregated columns (columns without an aggregate function applied, and not in the group-by). When you do this...

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate

http://dev.mysql.com/doc/refman/5.5/en/group-by-extensions.html

Mysql – Pair of 2-tuple composite FKs pointing to same table (closure table pattern)

This is a known limitation of InnoDB. It was reported as a bug way back in 2005:
Bug #11305 On Update Cascade not working with two references to the same field

The bug was acknowledged but not fixed. The following was added to the manual section http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html:

Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

Based on a workaround suggested in the bug, I got the update to work:

mysql> SET SESSION foreign_key_checks=0;

mysql> UPDATE father  SET `col1` = 'UPDATE_MY_ID_PLEASE'  WHERE `col1` = 'A' AND `col2` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE closure SET `a-col1`='UPDATE_MY_ID_PLEASE' WHERE `a-col1` = 'A' AND `a-col2` = 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> UPDATE closure SET `d-col1`='UPDATE_MY_ID_PLEASE' WHERE `d-col1` = 'A' AND `d-col2` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM father ; SELECT * FROM closure;
+---------------------+------+------+
| col1                | col2 | col3 |
+---------------------+------+------+
| A                   |    2 | A2   |
| B                   |    1 | B1   |
| B                   |    2 | B2   |
| UPDATE_MY_ID_PLEASE |    1 | A1   |
+---------------------+------+------+
4 rows in set (0.00 sec)

+---------------------+--------+---------------------+--------+--------+
| a-col1              | a-col2 | d-col1              | d-col2 | length |
+---------------------+--------+---------------------+--------+--------+
| A                   |      2 | A                   |      2 |      0 |
| A                   |      2 | B                   |      2 |      1 |
| UPDATE_MY_ID_PLEASE |      1 | B                   |      1 |      1 |
| UPDATE_MY_ID_PLEASE |      1 | UPDATE_MY_ID_PLEASE |      1 |      0 |
+---------------------+--------+---------------------+--------+--------+

Best Answer

Related Solutions

Mysql – How to get MAX and MIN in GROUP BY query

Mysql – Pair of 2-tuple composite FKs pointing to same table (closure table pattern)

Related Question