Using case statement before order by clause

Question

I would like to have a condition statement before Order By.

In my below query there is chance for Marks to be null, in that case I would like to Order by Rank Asc

SELECT TOP 1 P.[Score]
FROM dbo.[Profile] P
WHERE P.[ProfileId] = @ProfileId
ORDER BY P.[Marks] DESC AS [ProfileScore]

I tried changing it to the below way but it’s giving a compile error

SELECT TOP 1 P.[Score]
FROM dbo.[Profile] P
WHERE P.[ProfileId] = @ProfileId 
ORDER BY 
    (CASE WHEN P.[Marks] IS NOT NULL THEN P.[Marks] END) DESC,
    (CASE WHEN P.[Marks] IS NULL THEN P.[Rank] END) ASC AS [ProfileScore]

But it gives an error:

Incorrect syntax near the keyword 'DESC'

Answer 1

There is an AS [ProfileScore] too many in your ORDER BY. You cannot create aliases in ORDER BY.

If both Marks and Rank are numeric, I suggest:

SELECT TOP 1 P.[Score]
FROM dbo.[Profile] P
WHERE P.[ProfileId] = @ProfileId 
ORDER BY CASE WHEN P.[Marks] IS NULL THEN P.[Rank] ELSE -P.[Marks] END;