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?
Best Answer
IF This query is not correct then you can try this query