How do you ORDER BY a column when the CASE name is the same name as the column-CodePudding

I'm fairly new to SQL and am currently learning through Codecademy.

I'm doing a CASE statement query but I wanted to do a little extra by adding an ORDER BY.

The question is:

Use a CASE statement to change the rating system to: review greater than 4.5 is Extraordinary, review greater than 4 is Excellent, review greater than 3 is Good, review greater than 2 is Fair, Everything else is Poor. Don’t forget to rename the new column!

The answer it gave me is:

SELECT name,
  CASE
    WHEN review > 4.5 THEN 'Extraordinary'
    WHEN review > 4 THEN 'Excellent'
    WHEN review > 3 THEN 'Good'
    WHEN review > 2 THEN 'Fair'
    ELSE 'Poor'
  END AS 'Review'
FROM nomnom;

What I'm trying to do is add an ORDER BY for the column 'review' but it's ordering by the new CASE column 'Review' instead. How do I specify I want the original column 'review' to be ordered? Or do I have to rename the CASE column name?

My attempt:

SELECT name AS 'Name',
  CASE
    WHEN review > 4.5 THEN 'Extraordinary'
    WHEN review > 4 THEN 'Excellent'
    WHEN review > 3 THEN 'Good'
    WHEN review > 2 THEN 'Fair'
    ELSE 'Poor'
  END AS 'Review'
FROM nomnom
ORDER BY review DESC;

This results in an ORDER BY for the CASE statement column 'Review' instead of the wanted table column 'review'

Any additional advice or comments are appreciated!

CodePudding user response：

Repeat the CASE expression in the ORDER BY clause, but with no name. I know it seems like a lot of code and bad DRY, but it's really just one copy/paste operation and there are a number of places in SQL where we unfortunately have to break the "don't repeat yourself" rule.

Or maybe I misunderstood, and you want to order by the column name. Then you can fully-qualify the column name (ORDER BY nomnom.Review)