A CASE expression can bucket rows in GROUP BY or order them in ORDER BY.

The unlock is that GROUP BY and ORDER BY take expressions — so classification logic can live at query time: bucket rows by any rule (CASE), then order by any rule (a CASE mapping categories to ranks — the answer to 'alphabetical is wrong for these labels'). Two disciplines: write the CASE once (a drifted copy buckets and displays differently), and boundaries inherit case_order_first_match. When the mapping outgrows a CASE, promote to a lookup table.

SELECT
  CASE
        WHEN heat >= 8 THEN 'blazing'
        WHEN heat >= 4 THEN 'toasty'
        ELSE 'cool'
      END AS tier,
      COUNT(*) AS nFROM
      chili
    GROUP BY
      tier
  ORDER BY
  tier;

SELECT
  tier,
  n
FROM
  (
    SELECT
      CASE
        WHEN heat >= 8 THEN 'blazing'
        WHEN heat >= 4 THEN 'toasty'
        ELSE 'cool'
      END AS tier,
      COUNT(*) AS n,
      MIN(
        CASE
          WHEN heat >= 8 THEN 1
          WHEN heat >= 4 THEN 2
          ELSE 3
        END
      ) AS rk
    FROM
      chili
    GROUP BY
      tier
  )
ORDER BY
  rk;

The heat tiers printed blazing, cool, toasty — alphabetical, not the hottest-first podium — a CASE can bucket rows in GROUP BY and a rank-mapping CASE can order them, so the podium is blazing, toasty, cool.

Single-source the CASE; rank-mapping CASE for custom orders; promote to a mapping table at scale.