When I order by CreditRating, it is in order of the description, so "Above Average" comes first. I would like to have the order

1. Superior
2. Excellent
3. Above average
4. etc.

select VendorID as FournisseurID,
        V.Name as NomFournisseur, 
        CreditRating = 
        case
            when CreditRating = '1' then 'Superior' 
            when CreditRating = '2' then 'Excellent' 
            when CreditRating = '3' then 'Above average' 
            when CreditRating = '4' then 'Average' 
            when CreditRating = '5' then 'Below average'
        end,             
        sum(TotalDue) as TotalDû
from Purchasing.ProductVendor PV
Order by CreditRating, V.Name

CodePudding user response：

Just use a different name for your CASE expression and do the ORDER BY with the original column:

select V.VendorID as FournisseurID,
        V.Name as NomFournisseur, 
        case
            when V.CreditRating = '1' then 'Superior' 
            when V.CreditRating = '2' then 'Excellent' 
            when V.CreditRating = '3' then 'Above average' 
            when V.CreditRating = '4' then 'Average' 
            when V.CreditRating = '5' then 'Below average'
        end as CreditRatingText,             
        sum(V.TotalDue) as TotalDû
from Purchasing.ProductVendor V
order by V.CreditRating, V.Name

CodePudding user response：

ORDER BY has complicated binding rules.

If you specify a column with no table reference, it will be bound first to a matching column from the SELECT, rather than a column in the FROM. Only if no match is found are the columns in the FROM used.

Whereas if you specify a table reference, the original column in the FROM is always used.

See this fiddle for the difference.

So as a rule, if you really need to have the same name (which you should avoid for confusion's sake), then specify a table reference if you want the original column's sort

select VendorID as FournisseurID,
       V.Name as NomFournisseur, 
       CreditRating = 
        case CreditRating 
            when '1' then 'Superior' 
            when '2' then 'Excellent' 
            when '3' then 'Above average' 
            when '4' then 'Average' 
            when '5' then 'Below average'
        end,             
       sum(TotalDue) as TotalDû
from Purchasing.ProductVendor PV
GROUP BY PV.CreditRating, PV.Name, PV.VendorID
Order by PV.CreditRating, PV.Name