CODEWITHSUNDEEP
mysqlsqlenumsindexing
olamundo
olamundo

Reputation: 24991

How to get enum element from index

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?

Upvotes: 2

Views: 2793

Answers (2)

Norman Stevens
Norman Stevens

Reputation: 1

SUBSTR( MAX(CONCAT(0+column, column)), 2)

The concat will generate items like 0private or 1sergeant; these will be sorted as strings when taking the MAX. In this case this is the same as sorting by enum order. Finally the initial digit is removed by the substr.

If you have more than 10 items in your enum you'll have to format the numeric value with leading digits and remove the additional digits in the substr call.

Upvotes: 0

David Rabinowitz
David Rabinowitz

Reputation: 30448

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.

Upvotes: 2

Related Questions