CODEWITHSUNDEEP
sqlsql-serversql-order-by
LiIn
LiIn

Reputation:

order by case and alias

How can I use

ORDER BY 
CASE @AccountOperator
WHEN '>' THEN AccountTrxValue 
END ASC,
CASE @AccountOperator
WHEN '<' THEN AccountTrxValue 
END DESC

when AccountTrxValue is an alias and a result of the following in the "select"?

CASE WHEN PAA.RedeemValue>0 THEN
    PAA.RedeemValue * -1 
    ELSE PAA.EarnValue END
    AS AccountTrxValue ,

It dosesn't accept the alias because of the case

(without using sub query)

EDIT:

so someone answered me and I did it that way:

ORDER BY 
    CASE 
    WHEN @AccountOperator IS NULL OR @AccountOperator IN ('>','=') THEN
        CASE WHEN PAA.RedeemValue>0 THEN
        PAA.RedeemValue * -1 
        ELSE PAA.EarnValue  END 
    END DESC,  
CASE 
    WHEN @AccountOperator = '<'THEN
        CASE WHEN PAA.RedeemValue>0 THEN
        PAA.RedeemValue * -1 
        ELSE PAA.EarnValue  END 
    END

How can I write it in a shorter way? *I couldn't include the null in the "in" * I had to do 2 cases because it seems that "desc" should be written after the "end"

Upvotes: 1

Views: 1894

Answers (1)

gbn
gbn

Reputation: 432200

ORDER BY 
    CASE @AccountOperator
        WHEN '>' THEN AccountTrxValue 
        WHEN '<' THEN -AccountTrxValue
    END

You should be allowed to use column aliases in the ORDER BY clause, assuming SQL Server

Otherwise:

ORDER BY 
    CASE @AccountOperator
        WHEN '>' THEN
        CASE WHEN PAA.RedeemValue>0 THEN
        PAA.RedeemValue * -1 
        ELSE PAA.EarnValue END
    WHEN '<' THEN
        -CASE WHEN PAA.RedeemValue>0 THEN
        PAA.RedeemValue * -1 
        ELSE PAA.EarnValue END
END

Edit:

The -AccountTrxValue or -CASE WHEN PAA.RedeemValue>0... simply negates the value. This means the default ASC works like a DESC...

Edit2: General case for non-numerics

DECLARE @Switch char(1);
SELECT @Switch = '<';

SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY SortCol ASC) AS SortASC,
    ROW_NUMBER() OVER (ORDER BY SortCol DESC) AS SortDESC
FROM
    dbo.tablename
ORDER BY 
    CASE @Switch
        WHEN '<' THEN ROW_NUMBER() OVER (ORDER BY SortCol ASC)
        WHEN '>' THEN ROW_NUMBER() OVER (ORDER BY SortCol DESC)
        ELSE 0
    END;

Upvotes: 2

Related Questions