I have a table with multiple values of bed ID 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 a query to get a single row for each bed_id based on the value of status.
I've 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 right, but the rest of the row does not correspond to the row with minimum StatusOrder value)
What I want is:
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've read MySQL MIN/MAX all row 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 produces an error. (Also tried replacing StatusOrder with the full CASE statement)
NOTE: I've simplified the actual table which has many more columns. But basically I need access to the whole row that corresponds to the row with the lowest StatusOrder (determined by my case statement) for each bed_id.
Using MySQL 5.5