How to get an enum item from an index
I have a choice in which I do "group by". One of the columns is an enumeration, and I want to select the largest value for each group (that is, with the highest index in the enumeration). I can do
select MAX(enum_column+0) as enum_index
to get the largest index in the group, but how can I get the index of the enum back to the element of the enum?
Example: Let's say I have a table "soldiers" with three columns:
"id" - soldier ID
"name" is the soldier first name (a varchar field).
"rank" is the soldier rank, and it is an enum:
{'private','sergent'...,'lieutenant' etc.}
Now suppose I want to find, for every first name, the highest ranked person with that name. I can do:
select MAX(rank+0) as enum_index,name from soldiers group by name
But this will give me the index of the maximum element in the enum field, not the element name. I'll give it:
1 | john
2 | bob
where i want
'private' | john
'sergent' | bob
How can I achieve the desired result?
source to share
SUBSTR( MAX(CONCAT(0+column, column)), 2)
Concat will generate items like 0private
or 1sergeant
; they will be sorted as strings when received MAX
. In this case, it is the same as sorting by listing order. Finally, the leading digit is removed with substr
.
If your enumeration contains more than 10 items, you will have to format the numeric value with leading digits and remove the extra digits in the call substr
.
source to share