CODEWITHSUNDEEP
mysql
Jith
Jith

Reputation: 109

MYSQL which column for a row has the highest value

so I have a table like this in MYSQL

+---------+------+------+------+------+
| Names   | MAP1 | MAP2 | MAP3 | MAP4 |
+---------+------+------+------+------+
| Player1 | 2    | 3    | 1    | 4    |
+---------+------+------+------+------+
| Player2 | 1    | 4    | 3    | 2    |
+---------+------+------+------+------+
| Player4 | 3    | 2    | 1    | 1    |
+---------+------+------+------+------+

How would I go about selecting which map a player has the most on. So something like this

+---------+------+
| Names   | MAPS |
+---------+------+
| Player1 | MAP1 |
+---------+------+
| Player2 | MAP2 |
+---------+------+
| Player4 | MAP1 |
+---------+------+

Upvotes: 0

Views: 54

Answers (3)

symcbean
symcbean

Reputation: 48357

Strawberry is correct - you are hobbled by a very bad schema design.

There are various ways of achieving this with your current schema but they are all ugly hacks:

 DELIMITER $$
 CREATE FUNCTION maxmap(map1 INT, map2 INT, map3 INT, map4 INT)
 RETURNS TEXT
 BEGIN
   DECLARE maxmapt TEXT;
   DECLARE maxmapv INT;
   SET maxmapt = 'map1';
   SET maxmapv = map1 ;
   IF (map2 > map1) THEN
      SET maxmapt = 'map2';
      SET maxmapv = map2 ;
   END IF;
   IF (map3 > maxmapv) THEN
      SET maxmapt = 'map3';
      SET maxmapv = map3 ;
   END IF;
   IF (map4 > maxmapv) THEN
      SET maxmapt = 'map4';
   END IF;
   RETURN maxmapt;
 END;
 $$
 SELECT player, maxmap
 FROM yourtable
 $$

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

Fix your schema. A normalised design might look somewhat as follows, wherein names and map would be components of a PRIMARY KEY:

+---------+-----+-----+
| Names   | MAP | VAL |
+---------+-----+-----+
| Player1 |   1 |   2 |
| Player2 |   2 |   1 |
| Player4 |   3 |   3 |
| Player1 |   1 |   3 |
| Player2 |   2 |   4 |
| Player4 |   3 |   2 |
| Player1 |   1 |   1 |
| Player2 |   2 |   3 |
| Player4 |   3 |   1 |
| Player1 |   1 |   4 |
| Player2 |   2 |   2 |
| Player4 |   3 |   1 |
+---------+-----+-----+

From here, the problem is trivial.

Upvotes: 3

forpas
forpas

Reputation: 164099

Use the function greatest() in a CASE expression:

select Names,
  case greatest(MAP1, MAP2, MAP3, MAP4)
    when MAP1 then 'MAP1'
    when MAP2 then 'MAP2'
    when MAP3 then 'MAP3'
    when MAP4 then 'MAP4'
  end MAPS
from tablename

See the demo.
Results:

| Names   | MAPS |
| ------- | ---- |
| Player1 | MAP4 |
| Player2 | MAP2 |
| Player4 | MAP1 |

Upvotes: 0

Related Questions