Conditionally Order By Different Column

, 2018-10-01 (first published: )

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)

Related content

A Normalization Primer

For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.

5 (3)

2003-01-13

17,240 reads