SQL Server - Order by name from Subquery

Question

Below, my second case does not work because I'm referencing the name from the subquery. What is a good solution to this problem? Also, I'm declaring the SortBy parameter... just didn't include that in the example. Thank you so much for any help!

SELECT
 a.[PostID]
,a.[Title]
,a.[Date_Created] 
,(SELECT COUNT(VoteID) AS VoteCount 
     FROM [VoteTable] WHERE [PostID] = a.[PostID]) AS VoteCount

FROM [PostTable] a
INNER JOIN [Users] b
ON a.Created_UserID = b.UserID
WHERE a.Approved = 1

ORDER BY 
CASE @SortBy
    WHEN 1 THEN a.[Date_Created] END DESC,
CASE @SortBy
    WHEN 2 THEN [VoteCount] END DESC

Answer 1

Repeating the expression is one way, as is placing it in a subquery or CTE, e.g.:

;WITH cte AS
(
  SELECT
   a.PostID
  ,a.Title
  ,a.Date_Created 
  ,(SELECT COUNT(VoteID)  
     FROM VoteTable WHERE PostID = a.PostID) AS VoteCount
   FROM dbo.PostTable AS a
   INNER JOIN dbo.Users AS b
   ON a.Created_UserID = b.UserID
   WHERE a.Approved = 1
)
SELECT PostID, Title, Date_Created, VoteCount
FROM cte
ORDER BY 
  CASE @SortBy
    WHEN 1 THEN Date_Created END DESC,
  CASE @SortBy
    WHEN 2 THEN VoteCount END DESC;

But instead of repeating the expression, it doesn't seem to matter if you really perform a conditional for the second order by. So why not simply change your existing ORDER BY to:

ORDER BY CASE WHEN @SortBy = 1 THEN a.Date_Created END DESC,
         VoteCount DESC;

In this case if @SortBy is 2, the first expression is NULL, and so your desired ordering is still achieved. When @SortBy is 1, it's ordered by date descending, and unless there are a lot of ties with Date_Created and in that case you don't want to see those ties sorted by VoteCount DESC, the secondary order by is inconsequential, so you don't need to wrap it in a second CASE expression.

The reason, by the way, is that when you introduce a CASE expression to the ORDER BY, you change the way SQL Server might parse/evaluate the query, and you can no longer reference an alias from the SELECT list. This is why removing the CASE from the ORDER BY clause no longer complains about an invalid column name.

Answer 2

Replace:

WHEN 2 THEN [VoteCount] END DESC

With:

WHEN 2 THEN (SELECT COUNT(VoteID) AS VoteCount 
 FROM [VoteTable] WHERE [PostID] = a.[PostID]) END DESC