SQL Get the entire row based on the minimum value of the calculated column

Question

I have a table with several bed ID values ​​with different statuses, something like this.

PID | term_id | student_id | bed_id | status | Comment --------+------------+---------------+-----------+-----------+---------------- 1 | 29 | 1234 | 751 | Canceled | Not this one 2 | 29 | 1234 | 751 | Active | This one 3 | 29 | 531 | 752 | Active | This one too 4 | 29 | 823 | 752 | Canceled | Not this one either 5 | 29 | 525 | 753 | Canceled | But this one too 

I want the request to get one row for each bed_id based on the status value.
I tried:

 SELECT *,MIN(CASE sample.status WHEN 'Arrived' THEN 1 WHEN 'Active' THEN 2 WHEN 'Pending Approval' THEN 3 WHEN 'Pending Confirmation' THEN 4 WHEN 'Pending Manual' THEN 5 WHEN 'Denied' THEN 6 WHEN 'Canceled' THEN 7 END) AS StatusOrder FROM sample WHERE (sample.term_id = 29) GROUP BY bed_id 

But it gives me:

 PID | term_id | student_id | bed_id | status | Comment | StatusOrder ------------------------------------------------------------------------------------ 1 | 29 | 1234 | 751 | Canceled | Not this one | 2 3 | 29 | 531 | 752 | Active | This one too | 2 5 | 29 | 525 | 753 | Canceled | But this one too | 7 

(The StatusOrder value is correct, but the rest of the line does not match the line with the minimum StatusOrder value)

I want to:

 PID | term_id | student_id | bed_id | status | Comment | StatusOrder ------------------------------------------------------------------------------------ 2 | 29 | 1234 | 751 | Active | This one | 2 3 | 29 | 531 | 752 | Active | This one too | 2 5 | 29 | 525 | 753 | Canceled | But this one too | 7 

I read MySQL MIN / MAX all the lines and also tried this:

 SELECT *,MIN(CASE sample.status WHEN 'Arrived' THEN 1 WHEN 'Active' THEN 2 WHEN 'Pending Approval' THEN 3 WHEN 'Pending Confirmation' THEN 4 WHEN 'Pending Manual' THEN 5 WHEN 'Denied' THEN 6 WHEN 'Canceled' THEN 7 END) AS StatusOrder FROM sample WHERE ( (sample.term_id = 29) AND ( StatusOrder = CASE sample.status WHEN 'Arrived' THEN 1 WHEN 'Active' THEN 2 WHEN 'Pending Approval' THEN 3 WHEN 'Pending Confirmation' THEN 4 WHEN 'Pending Manual' THEN 5 WHEN 'Denied' THEN 6 WHEN 'Canceled' THEN 7 END) ) GROUP BY bed_id 

But this leads to an error. (Also tried replacing StatusOrder with the full CASE statement)

NOTE. I simplified the actual table with a lot of columns. But basically, I need access to the entire row corresponding to the row with the lowest StatusOrder (defined by my case expression) for each bed_id.

Using MySQL 5.5

Answer 1

The “below query” works, but it’s better to consider replacing the column status with status_code code and have a separate status(status_code,description) table status(status_code,description) {denormalise}

And indexing ( term_id,statUs_code ).

with this we can just join. Instead, create this view.

 SELECT * FROM (SELECT *,(CASE sample.status WHEN 'Arrived' THEN 1 WHEN 'Active' THEN 2 WHEN 'Pending Approval' THEN 3 WHEN 'Pending Confirmation' THEN 4 WHEN 'Pending Manual' THEN 5 WHEN 'Denied' THEN 6 WHEN 'Canceled' THEN 7 END) AS status_code FROM SAMPLE WHERE sample.term_id = 29 ) my_view1, (SELECT BED_ID,MIN(CASE sample.status WHEN 'Arrived' THEN 1 WHEN 'Active' THEN 2 WHEN 'Pending Approval' THEN 3 WHEN 'Pending Confirmation' THEN 4 WHEN 'Pending Manual' THEN 5 WHEN 'Denied' THEN 6 WHEN 'Canceled' THEN 7 END) AS status_code FROM SAMPLE WHERE sample.term_id = 29 GROUP BY BED_ID ) my_view2 WHERE my_view1.bed_id = my_view2.bed_id AND my_view1.status_code = my_view2.status_code