title: How to get enum element from index
body: <p>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 </p>
  
  <pre><code>select MAX(enum_column+0) as enum_index 
  </code></pre>
  
  <p>to get the the largest index in the group, but how can I turn the enum index back to the enum item?</p>
  
  <hr>
  
  <p>Example:
  Say I have a table "soldiers" with three columns:</p>
  
  <pre><code>"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.} 
  </code></pre>
  
  <p>Now suppose I want to find for each first name, the the highest rank a person with that name has.
  I can do:</p>
  
  <pre><code>select MAX(rank+0) as enum_index,name from soldiers group by name
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>1 | john
  2 | bob
  </code></pre>
  
  <p>where I want </p>
  
  <pre><code>'private' | john
  'sergent' | bob 
  </code></pre>
  
  <p>How can I achieve the desired result?</p>