Ordering by min value in result, while respecting grouping in mysql

Question

Second question in two days on this same sort of topic.

I currently am using the following query:

SELECT name,suite,webpagetest.id,MIN(priority) AS min_pri
FROM webpagetest,comparefileerrors
WHERE vco="aof" AND user="1" AND calibreversion="9" 
AND webpagetest.id=comparefileerrors.id 
AND comparefileerrors.priority IS NOT NULL 
GROUP BY id,suite 
ORDER BY COALESCE(suite,name),min_pri 
ASC ;

This gives me results that look as follows:

+-----------------------------+-----------------------------+-------+---------+
| name                        | suite                       | id    | min_pri |
+-----------------------------+-----------------------------+-------+---------+
| bz1273_cmdline_execplussvdb | NULL                        |  6203 |       2 | 
| bz1508_SEGV_password        | NULL                        |  6185 |       2 | 
| bz1747_bad_lvsf             | NULL                        | 36683 |       1 | 
| set_get_status              | shortsRepairDB_2009.1_suite |  6193 |       0 | 
| u2uDemo                     | shortsRepairDB_2009.1_suite |  6195 |       0 | 
| change_sets                 | shortsRepairDB_2009.1_suite |  6194 |       0 | 
| add_delete_mask_polygon     | shortsRepairDB_2009.1_suite |  6191 |       0 | 
| isolate_shorts              | shortsRepairDB_2009.1_suite |  6196 |       0 | 
| add_delete_text             | shortsRepairDB_2009.1_suite |  6197 |       0 | 
| assign_short_AND_user_info  | shortsRepairDB_2009.1_suite |  6198 |       2 | 
| comment_short               | shortsRepairDB_2009.1_suite |  6192 |       2 | 
+-----------------------------+-----------------------------+-------+---------+

However, what I would like to do is order them by the minimum priority that is encountered in suite, if there is one. Suite is an optional field, and if it is null, then there is no need to do this grouping. I want to use the minimum value in the suite determine the overall placement of the suite.

I am heavily considering redesigning my app to use PHP to do this sorting, but for the mean time, it would be nice to do this with mysql.

The results should look like the following:

+-----------------------------+-----------------------------+-------+---------+
| name                        | suite                       | id    | min_pri |
+-----------------------------+-----------------------------+-------+---------+
| set_get_status              | shortsRepairDB_2009.1_suite |  6193 |       0 | 
| u2uDemo                     | shortsRepairDB_2009.1_suite |  6195 |       0 | 
| change_sets                 | shortsRepairDB_2009.1_suite |  6194 |       0 | 
| add_delete_mask_polygon     | shortsRepairDB_2009.1_suite |  6191 |       0 | 
| isolate_shorts              | shortsRepairDB_2009.1_suite |  6196 |       0 | 
| add_delete_text             | shortsRepairDB_2009.1_suite |  6197 |       0 | 
| assign_short_AND_user_info  | shortsRepairDB_2009.1_suite |  6198 |       2 | 
| comment_short               | shortsRepairDB_2009.1_suite |  6192 |       2 | 
| bz1747_bad_lvsf             | NULL                        | 36683 |       1 | 
| bz1273_cmdline_execplussvdb | NULL                        |  6203 |       2 | 
| bz1508_SEGV_password        | NULL                        |  6185 |       2 | 
+-----------------------------+-----------------------------+-------+---------+

Answer 1

You can add the lowest priority for a suite as a column. Assuming the suite is in webpagetest and the priority in comparefileerrors, something like:

SELECT name,suite,webpagetest.id, MIN(priority) AS min_pri,
    (select min(wt2.priority) 
     from webpagetest wt2 
     inner join comparefileerrors cfe2
         on wt2.id = cfe2.id
     where wt2.suite = wt.suite) as suite_min_pri
FROM webpagetest wt, comparefileerrors cfe

And then use that in the order by:

ORDER BY COALESCE(suite,name), suite_min_pri

Answer 2

If you have values for priority in the range [0,9], you can add another column for combined priority, and let that value be 10 * (if suite exists ? 1 : 0) + priority

SELECT 
    name,suite,webpagetest.id,MIN(priority) AS min_pri
    (CASE WHEN suite IS NOT NULL THEN 1 ELSE 0 END)*10+priority as combined_pri
FROM webpagetest,comparefileerrors
WHERE vco="aof" AND user="1" AND calibreversion="9" 
AND webpagetest.id=comparefileerrors.id 
AND comparefileerrors.priority IS NOT NULL 
GROUP BY id,suite 
ORDER BY COALESCE(suite,name),combined_pri 
ASC ;

if the priority is in a larger set of numbers, just increase the multiplier. The key is to have it be one digit larger than the biggest value of priority.