How to retrieve favorite sport name by checking multiple sport columns in MySQL?

Question

I have multiple columns that have the name of a sport and a FLOAT value that acts as a percentage of how much one likes the sport.

For example,

Table: likes

Field A: Soccer (94.27 %) 
Field B: Football (64.21 %)
Field C: Baseball (1.52 %)

I want to search the fields for the highest percentage, and then return the name of the favorite sport. In this case, it would return 'soccer'.

I have the following MySQL script that retrieves the highest percentage.

-- Get favorite sport  (soccer, 94.2716%)
SELECT GREATEST(likes_soccer, likes_football, likes_baseball, likes_basketball, likes_gymnastics, 
    likes_volleyball, likes_running, likes_swimming, likes_karate, likes_biking, likes_wrestling, likes_golf) AS FAVORITE_SPORT
FROM likes l
WHERE l.child_id = 32;

However, this isn't exactly what I want. But it does output soccer as having 94.27%, which is the highest percentage. However, I am wanting this to return 'soccer', as well as a string representation for any of the other sports. How can I accomplish this?

Your design is flawed. Have one column for sports and one column for percentages — Strawberry, Jul 23 '17 at 23:46
Each child record has different values for these fields, which is why its designed the way it is. While child_id 1 might like soccer at 97%, child_id 2 might have soccer set at 5%. Thus it made sense having it designed this way so each record can store its own values for sports. — Phil, Jul 23 '17 at 23:50
Nope. It may make sense to you, but it's not how data is stored in relational databases. — Strawberry, Jul 23 '17 at 23:59
This solved my question: https://stackoverflow.com/questions/12195558/greatest-value-of-multiple-columns-with-column-name — Phil, Jul 24 '17 at 00:01
As for what's flawed, I'm not sure, and I might have to just learn that by experience. You're welcome to share a solution. — Phil, Jul 24 '17 at 00:22
I thought I did . Fix the design and the problem becomes trivial — Strawberry, Jul 24 '17 at 03:21
I don't get it still. I think what you're saying is have two tables: PLAYERS and SPORTS. SPORTS contains the player's id, along with sport name, id, and percentage. Say there's 8 sports. The SPORTS table would have 8 sports records per player then. While I thought of this before, I felt it was easier to keep a single record with the columns, and retrieve it with the solution below. But I'm wondering if that's what you're suggesting. — Phil, Jul 24 '17 at 05:04
Yes, that's it (except you'd have 3 tables: p, s, and ps - the ps table would store the above). Do that instead. It's a thousand times better than your present 'solution'! Honestly, if you insist on sticking with your current design then you may as well not bother with an RDBMS at all! — Strawberry, Jul 24 '17 at 06:00
It started out with saving directly to file, and now I have the data in a database. Maybe my fault doing a direct transfer than thinking it through. At least I'm in the beginning stage. Thanks for the thoughts. — Phil, Jul 24 '17 at 06:22

score 0 · Answer 1 · answered Jul 24 '17 at 00:03

The following solved the problem:

SELECT @var_max_val:= GREATEST(col1, col2, col3, ...) AS max_value,
       CASE @var_max_val WHEN col1 THEN 'col1'
                         WHEN col2 THEN 'col2'
                         ...
       END AS max_value_column_name
FROM table_name
WHERE ...

Source: Greatest value of multiple columns with column name?

How to retrieve favorite sport name by checking multiple sport columns in MySQL?

1 Answers1