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