2

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?

  • 2
    You do need risklevel. This is really a "groupwise max" problem. (That link has more efficient ways to do it.) – Rick James Aug 30 '16 at 20:14
  • @Rick James thx rick, the left join is very nice – omri Aug 31 '16 at 8:11
-4
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 )
  • 2
    I don't think this is correct. – ypercubeᵀᴹ Feb 17 '17 at 17:58
  • The second query is not even wrong. – ypercubeᵀᴹ Feb 18 '17 at 20:20

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.