MYSQL JOIN with MAX() value of ENUM

Question

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

Answer 1

as you probably know the MAX and ORDER BY on the enum work with String and not with int.

Try to edit you query removing the limit 1 and selecting only the status. You'll see that the proper order is not given. This is the order by status DESC of the enum: TWO, THREE, ONE.

So both the order by and max() works with string.

My suggestion is to use an INT instead of a VARCHAR for the statuses.

Furthermore, I have no idea how to join them, is it possible to do this? Do I need a subquery?

With a nested query you can retrieve the Max value for an integer enum, and put this retrieved value in the outer query where condition.

Answer 2

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.