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