How to get MAX and MIN in GROUP BY query?

In this query:

SELECT col1, col2, col3
FROM table1
GROUP BY col1

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

How to select MAX and MIN for each value of col1.

Something like:

SELECT col1, minimum of col2, maximum of col2, minimum of col3, maximum of col3

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

StackExchange Database Administrators Q#48203, answer score: 17