Conditionally Order By Different Column

,

I came across a situation yesterday that I don’t remember encountering before so I figured it would be worth sharing. The objective was to get the top 5 Card Numbers for each BIN based on the absolute value of either the CreditAmount or DebitAmount. I figured I needed to use a CASE statement which I have done plenty of times, but never quite like this.

IF OBJECT_ID('tempdb.dbo.#MyTestTable', 'U') IS NOT NULL
    DROP TABLE #MyTestTable;

CREATE TABLE #MyTestTable
(
    BIN VARCHAR(6)
  , CardNumber VARCHAR(4)
  , CreditAmount DECIMAL(18, 2)
  , DebitAmount DECIMAL(18, 2)
);

INSERT INTO #MyTestTable
VALUES
('123456', '3185', 27668.12, -7512.86)
, ('123456', '9911', 15966.74, -10864.46)
, ('123456', '0961', 5885.91, -12718.75)
, ('123456', '4428', 14718.67, -9005.07)
, ('123456', '4775', 2528.38, -13184.76)
, ('123456', '5868', 12412.72, 0.00)
, ('123456', '0390', 0.00, -12244.86)
, ('987654', '4099', 11224.84, -1398.00)
, ('987654', '0275', 4100.00, -10581.46)
, ('987654', '8511', 11024.32, -18577.88)
, ('987654', '8323', 10355.93, -10961.63)
, ('987654', '4950', 10813.85, -8669.67)
, ('987654', '3687', 10315.09, 0.00)
, ('987654', '3600', 9937.34, -10403.67);

SELECT   rnkd.BIN
       , rnkd.CardNumber
       , rnkd.CreditAmount
       , rnkd.DebitAmount
       , rnkd.Position
FROM     (
             SELECT BIN
                  , CardNumber
                  , CreditAmount
                  , DebitAmount
                  , ROW_NUMBER() OVER (PARTITION BY BIN
                                       ORDER BY CASE
                                                    WHEN ABS(DebitAmount) > CreditAmount THEN
                                                        ABS(DebitAmount)
                                                    ELSE
                                                        CreditAmount
                                                END DESC
                                      ) AS Position
             FROM   #MyTestTable
         ) AS rnkd
WHERE    rnkd.Position <= 5
ORDER BY rnkd.BIN
       , rnkd.Position;

Rate

3 (4)

Share

Share

Rate

3 (4)