How do I order categorical data in SQL to match a list of names?

How would I order a column of categories based on a specified (and ordered) list of just those category names?

For example, let’s say the names of groups a, b, and c, are listed in descending order based on their respective group mean blood pressure as [b,a,c] (with b having the highest mean blood pressure, and c the lowest). Going back to the full dataset, how could I organize, say 100 rows, by order of the category in that list, and then by individual blood pressure.

Here’s what I guessed, and this didn’t work.

SELECT * 
FROM data
ORDER BY category IN (SELECT category FROM data
                      GROUP BY category
                      ORDER BY AVG(blood_pressure) DESC)

I would still like to order by individual blood pressure within their respective groups, but ideally have all the rows grouped first by their category mean.

Answer

You do it like this — join to the “table” that contains what you want to sort on then specify that in the order by, in your case we can create the table to use in a sub-select.

SELECT * 
FROM data
JOIN (
  SELECT category,  AVG(blood_pressure)  as AVG_BP 
  FROM data
  GROUP BY category
) x on x.category = data.category
ORDER BY AVG_BP DESC