Could you tell me why my ORDER BY DESC function is not working properly?-CodePudding

SELECT 
    YEAR, 
    Strata, 
    Strata_Name, 
    FORMAT([%]/10000, 'P') [%] 
FROM 
    [dbo].[Depression_in_California] 
WHERE 
    Strata_Name = 'Male' 
ORDER BY [%] desc;

the column is float not null

CodePudding user response：

Because you are converting the float value to a string and then sorting. The solution is to fix your format to have a fixed number of decimals and align right. The other solution is to also select the percent column and sort by that column.

Note: I think that formatting and other such operations should be relegated to the GUI not SQL

CodePudding user response：

You have a numeric column called [%] and you are naming your calculated column [%] too. The type of your calculated value if nvarchar.

As pointed out by @Tarik, it looks like SQL Server is ordering the result by the calculated column (alias). I think it would be much more clear to use a different alias name, for example:

SELECT 
    YEAR, 
    Strata, 
    Strata_Name, 
    FORMAT([%]/10000, 'P') PercentageOfDepression /* <-- calculated nvarchar */
FROM 
    [dbo].[Depression_in_California] 
WHERE 
    Strata_Name = 'Male' 
ORDER BY [%] desc; /* <-- numeric [%] */