Mysql GROUP by a key and pick the higher value

Question

I have been trying to group by by a certain key but the only higher value. for example lets say this is my data

    ID      KEY      Name      Value1     Value2
    1        52      James      0.5         0
    2        52      Amy        0.2         0.1
    3        65      Zeus       0.8         0.3
    4        65      Chris      0.1         0

So when I do

    SELECT * FROM MyTable GROUP BY KEY

The results I get is

   ID      KEY      Name      Value1     Value2
   2        52      Amy        0.2         0.1
   4        65      Chris      0.1         0

The result I want is,

    ID      KEY      Name      Value1     Value2
    1        52      James      0.5         0
    3        65      Zeus       0.8         0.3

I want it to Group by KEY but select the the person with the higher Value1 that belong to the certain KEY. I do not want to use a

   WHERE Value1 > 0.4

There are thousand of records and a where statement wouldn't help. I want to know if there is a way to compare Value1 or Value2 between the pertaining KEY and pick the higher value when grouping.

Hope this was clear and thank you in advance. :)

score 2 · Accepted Answer · answered Dec 23 '16 at 18:19

2

First a query to get the keys and the max value

SELECT KEY, MAX(Value1) as mValue1
FROM yourTable
GROUP BY KEY

Now join back to get the rest of the rows

SELECT yourTable.*
FROM yourTable
JOIN (
  SELECT KEY, MAX(Value1) as mValue1
  FROM yourTable
  GROUP BY KEY
) AS sub on Sub.KEY = yourTable.KEY and Sub.mValue1 = yourTable.Value1

Ta da!

answered Dec 23 '16 at 18:19

Hogan

69,564
10
76
117

1. `key` is reserved. 2. if multiple rows with same value1, your query return all those rows. I think only top should be returned. – Gurwinder Singh Dec 23 '16 at 18:22
@GurwinderSingh -- 1. yes "KEY" is reserved, "yourTable" is also not the name of his table -- I'm hopeful the OP can deal with these issues. 2) the OP needs to say what happens if more than one row is returned since the specification does not say what to do, just fetch first or top will cause the same issue he is dealing with now. – Hogan Dec 23 '16 at 18:26
thanks! yea i figured the kinks out and it worked!. Again Thanks all! – razerer Dec 23 '16 at 23:04

Mysql GROUP by a key and pick the higher value

1 Answers1