Using order by and direction with case when but error sorting by secondary column-CodePudding

Below works fine:

ORDER BY
 CASE WHEN @SortDirection = 1 AND @SortBy = 1 THEN announce.[Status] END ASC,
 CASE WHEN @SortDirection = 2 AND @SortBy = 1 THEN announce.[Status] END DESC 
 ...

What I want to do is apply a secondary sort order in each case which is same regardless of direction:

ORDER BY
 CASE WHEN @SortDirection = 1 AND @SortBy = 1 THEN announce.[Status] END ASC, announce.ID Desc,
 CASE WHEN @SortDirection = 2 AND @SortBy = 1 THEN announce.[Status] END DESC, announce.ID Desc,
 CASE WHEN @SortDirection = 1 AND @SortBy = 2 THEN announce.[Title] END ASC, announce.Date Desc,
 CASE WHEN @SortDirection = 2 AND @SortBy = 2 THEN announce.[Title] END DESC, announce.Date Desc

But above gives me error:

A column has been specified more than once in order by list. Columns in order by list must be unique.

CodePudding user response：

You don't need to repeat the sort criteria for both directions if they're the same in either case, and they don't have to be right next to the other criteria either. Only one of the first four CASE expressions can possibly return true, and also only of the last two:

ORDER BY
 CASE WHEN @SortDirection = 1 AND @SortBy = 1 THEN announce.[Status] END ASC, 
 CASE WHEN @SortDirection = 2 AND @SortBy = 1 THEN announce.[Status] END DESC,
 CASE WHEN @SortDirection = 1 AND @SortBy = 2 THEN announce.[Title] END ASC, 
 CASE WHEN @SortDirection = 2 AND @SortBy = 2 THEN announce.[Title] END DESC,
 CASE WHEN @SortBy = 1 THEN announce.ID END DESC,
 CASE WHEN @SortBy = 2 THEN announce.Date END DESC;

CodePudding user response：

Given @SortDirection = 1 and @SortBy = 1 your sorts will translate to something like this:

--Sort 1
ORDER BY
 announce.[Status] ASC,
 NULL DESC
...
--Sort 2
ORDER BY
 announce.[Status] ASC, announce.ID Desc,
 NULL DESC, announce.ID Desc,
 NULL ASC, announce.Date Desc,
 NULL DESC, announce.Date Desc

Maybe try something like this:

ORDER BY
 CASE WHEN @SortDirection = 1 AND @SortBy = 1 THEN announce.[Status] END ASC,
 CASE WHEN @SortDirection = 2 AND @SortBy = 1 THEN announce.[Status] END DESC,
 CASE WHEN @SortDirection = 1 AND @SortBy = 2 THEN announce.[Title] END ASC,
 CASE WHEN @SortDirection = 2 AND @SortBy = 2 THEN announce.[Title] END DESC, 
announce.ID Desc,
announce.Date Desc