Mysql - Grouping and getting the Max(col1) row variables

Question

I have this table:

CREATE TABLE `table1` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `col1` VARCHAR(30) NULL DEFAULT NULL,
 `col2` VARCHAR(30) NULL DEFAULT NULL,
 `col3` VARCHAR(30) NULL DEFAULT NULL,
 `riskLevel` VARCHAR(30) NULL DEFAULT NULL
 PRIMARY KEY (`id`)
)COLLATE='utf8_general_ci' ENGINE=InnoDB;

What i want to do is group this table by col1, and get the row with the highest riskLevel for each col1 group values.

When using this query:

SELECT id,col1,col2,col3, MAX(riskLevel) 
FROM table1
GROUP BY col1

You get the max riskLevel, but the other values are random, not fittings to the correct row.

I know one solution:

SELECT id,col1,col2,col3, riskLvel
FROM table1 JOIN
(SELECT col1, MAX(riskLevel) as riskLevel
FROM table1
GROUP BY col1) x ON x.col1 = table1.col1 AND x.riskLevel = table1.riskLevel

This solution doesn't seem optimal to me.

Is there another way of getting the described behavior?

• Rick James: You do need risklevel. This is really a "groupwise max" problem. (That link has more efficient ways to do it.)
• omri: @Rick James thx rick, the left join is very nice

Answer 1

SELECT id,col1,col2,col3, riskLvel
FROM table1
where risklevel IN ( SELECT MAX(riskLevel)
 FROM table1
 GROUP BY col1 )

IF This query is not correct then you can try this query

SELECT id,col1,col2,col3, riskLvel
 FROM table1
 where col1 IN ( SELECT MAX(col1)
 FROM table1
 GROUP BY col1 )

• ypercubeᵀᴹ: I don't think this is correct.
• ypercubeᵀᴹ: The second query is not even wrong.