MYSQL: How to get enum element from index

Question

I have a select in which I do a "group by". One of the columns is an enum, and I want to select the biggest value for each group (i.e the one with the largest index in the enum). I can do

select MAX(enum_column+0) as enum_index

to get the the largest index in the group, but how can I turn the enum index back to the enum item?

---

Example: Say I have a table "soldiers" with three columns:

"id" - soldier's ID
"name" is the soldier's first name (a varchar field).
"rank" is the soldier's rank, and it is an enum: 
{'private','sergent'...,'lieutenant' etc.}

Now suppose I want to find for each first name, the the highest rank a person with that name has. I can do:

select MAX(rank+0) as enum_index,name from soldiers group by name

But that will give me the index of the max element in the enum field, and not the name of the element. I.e it will give me:

1 | john
2 | bob

where I want

'private' | john
'sergent' | bob

How can I achieve the desired result?

Answer 1

Run the following

CREATE TABLE ranks
SELECT DISTINCT rank+0 as id, CONCAT('',rank) as rank
FROM soldiers

Then join the data with the soldiers table. Notice it will give you only the ranks actually in use.