CODEWITHSUNDEEP
sqlsql-servert-sql
Ryan
Ryan

Reputation: 867

ORDER BY CASE WHEN can't be used on an COUNT() AS Foo result column?

I've got a query in which I am trying to allow sorting based on a parameter passed in by a C# SQL Server client library. My query currently looks like this ...

SELECT d.OpinionDocumentId,
       d.DisplayTitle, 
       d.Court,
       d.State,
       d.Date,
       (SELECT i.Identifier + ', '
        FROM dbo.OpinionDocumentCitationIdentifiers i
        WHERE i.OpinionDocument_OpinionDocumentId = b.OpinionDocument_OpinionDocumentId
        FOR XML PATH('')) AS Identifiers,
       COUNT(DISTINCT c.OpinionDocument_OpinionDocumentId1) AS CitationCount,
       COUNT(DISTINCT t.OpinionDocument_OpinionDocumentId) AS CitedByCount

FROM dbo.OpinionDocumentBookmarks b

INNER JOIN dbo.OpinionDocuments d ON d.OpinionDocumentId = b.OpinionDocument_OpinionDocumentId
LEFT JOIN dbo.OpinionDocumentOpinionDocuments c ON c.OpinionDocument_OpinionDocumentId = b.OpinionDocument_OpinionDocumentId
LEFT JOIN dbo.OpinionDocumentOpinionDocuments t ON t.OpinionDocument_OpinionDocumentId1 = b.OpinionDocument_OpinionDocumentId

WHERE b.Account_AccountId = @accountId

GROUP BY b.OpinionDocumentBookmarkId,
       b.OpinionDocument_OpinionDocumentId,
       d.OpinionDocumentId,
       d.DisplayTitle,
       d.Court,
       d.State,
       d.Date

ORDER BY CASE WHEN @sort = 'DisplayTitle' THEN d.DisplayTitle END,
       CASE WHEN @sort = 'Date' THEN d.Date END,
       CASE WHEN @sort = 'Court' THEN d.Court END,
       CASE WHEN @sort = 'State' THEN d.State END,
       CASE WHEN @sort = 'Citations' THEN CitationCount END

OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY

As you can see, I'm trying to allow sorting based on a parameter. I'm trying to allow sorting on the aggregated CitationCount or CitedByCount columns. I can sort by these columns when I do not use the ORDER BY CASE WHEN, but when I try to specify that as a column in the ORDER BY CASE WHEN I get an error saying that it's not a column.

Is this possible?

Upvotes: 0

Views: 105

Answers (1)

Peter Lalovsky
Peter Lalovsky

Reputation: 36

SELECT *
FROM
    (
    SELECT
        d.OpinionDocumentId
        , d.DisplayTitle
        , d.Court
        , d.[State]
        , d.[Date]
        , (
            SELECT i.Identifier + ', '
            FROM dbo.OpinionDocumentCitationIdentifiers AS i
            WHERE i.OpinionDocument_OpinionDocumentId = b.OpinionDocument_OpinionDocumentId
            FOR XML PATH('')
        ) AS Identifiers
        , COUNT(DISTINCT c.OpinionDocument_OpinionDocumentId1) AS CitationCount
        , COUNT(DISTINCT t.OpinionDocument_OpinionDocumentId) AS CitedByCount
    FROM
        dbo.OpinionDocumentBookmarks AS b
        INNER JOIN dbo.OpinionDocuments AS d
            ON d.OpinionDocumentId = b.OpinionDocument_OpinionDocumentId
        LEFT JOIN dbo.OpinionDocumentOpinionDocuments AS c
            ON c.OpinionDocument_OpinionDocumentId = b.OpinionDocument_OpinionDocumentId
        LEFT JOIN dbo.OpinionDocumentOpinionDocuments AS t
            ON t.OpinionDocument_OpinionDocumentId1 = b.OpinionDocument_OpinionDocumentId
    WHERE b.Account_AccountId = @accountId
    GROUP BY
        b.OpinionDocumentBookmarkId
        , b.OpinionDocument_OpinionDocumentId
        , d.OpinionDocumentId
        , d.DisplayTitle
        , d.Court
        , d.State
        , d.Dat
    ) AS H
ORDER BY
    CASE WHEN @sort = 'DisplayTitle' THEN DisplayTitle END,
    CASE WHEN @sort = 'Date' THEN [Date] END,
    CASE WHEN @sort = 'Court' THEN Court END,
    CASE WHEN @sort = 'State' THEN [State] END,
    CASE WHEN @sort = 'Citations' THEN CitationCount END

Upvotes: 2

Related Questions