CODEWITHSUNDEEP
sqlsql-order-by
Freddmo
Freddmo

Reputation: 1

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

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

enter image description here

Upvotes: 0

Views: 207

Answers (2)

Hooman Bahreini
Hooman Bahreini

Reputation: 15559

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 [%] */

Upvotes: 0

Tarik
Tarik

Reputation: 11209

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

Upvotes: 2

Related Questions