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; 
