MYSQL JOIN with MAX() value of ENUM

Viewed 1066

I have tow tables in my database:

user_id | user_name
--------+----------
1       |jim
2       |john

user_id | status
--------+----------
1       |ONE
2       |TWO
1       |THREE

The status is an enum, defined like that:

status enum('ONE','TWO','THREE') NOT NULL

This should garantee that ONE is the lowest status and THREE the highest. Im retrieving the status of a user in the following way:

 SELECT user_id, type, status, event_date, valid_from, valid_to
 FROM status_event
 WHERE user_id = 1
 ORDER BY status DESC
 LIMIT 1;

This gives me, like expected, the status THREE as order by is using the order of the status.

Now I want to write SQL to retrieve a list of all users and their highest status value. Im having trouble as the MAX() funtion is evidently comparing the status values as strings and returning TWO as max. Furthermore, I have no idea how to join them, is it possible to do this? Do I need a subquery?

The result I want is the following:

user_name| status
---------+----------
jim      |THREE
john     |TWO
3 Answers

Another possible solution is to create an "enum mapping" table that maps each value of the enum to a integer. You can then join that table to your query and use MIN of the integer value rather than the enum.

Related