SQL question?

Database
Enthusiast

SQL question?

SEL  CAD.CUST_ACCT_KEY, CATD.CUST_ACCT_MKT_DESC,  CASD.CUST_ACCT_STAT_CODE, ETD.RCVR_TYPE_DESC, ETD.EQUIP_BUS_CAT_DESC  

FROM CUST_ACCT_DIM AS CAD

  JOIN CUST_ACCT_STAT_DIM AS CASD 

     ON CAD.CURR_CUST_ACCT_STAT_KEY = CASD.CUST_ACCT_STAT_KEY

  JOIN CUST_ACCT_TYPE_DIM AS CATD 

     ON CATD.CUST_ACCT_TYPE_KEY=CAD.CURR_CUST_ACCT_TYPE_KEY

 JOIN CUST_ACCT_ACRD_XREF CAAX 

     ON CAD.CUST_ACCT_KEY = CAAX.CUST_ACCT_KEY

 JOIN EQUIP_TYPE_DIM ETD

  ON CAAX.IRD_MFR_BRAND_NAME = ETD.MFR_BRAND_NAME         

  AND CAAX.IRD_MFR_MDL_CODE = ETD.MFR_MDL_CODE 

 

WHERE CASD.CUST_ACCT_STAT_CODE IN ('ACTV', 'PSUS', 'PDIS', 'SUSP')

      AND CATD.CUST_ACCT_MKT_DESC = 'RESIDENTIAL'

I have this query where I want to rank by ETD.RCVR_TYPE_DESC 

So if the value is 

WHEN RCVR_TYPE_DESC = 'AAA' THEN 1

WHEN RCVR_TYPE_DESC = 'BBB' THEN 2

WHEN RCVR_TYPE_DESC = 'CCC' THEN 3

WHEN RCVR_TYPE_DESC = 'DDD' THEN 4

and then select only the CAD.CUST_ACCT_KEY and two other columns (ETD.RCVR_TYPE_DESC, ETD.EQUIP_BUS_CAT_DESC) for the lowest rank number.

How can I do that?

Thanks in advance.

Tags (3)
1 REPLY
Senior Apprentice

Re: SQL question?

Simply write your condition into the RANK:

qualify
rank()
over (partition by CAD.CUST_ACCT_KEY or whatever you need
order by case
WHEN RCVR_TYPE_DESC = 'AAA' THEN 1
WHEN RCVR_TYPE_DESC = 'BBB' THEN 2
WHEN RCVR_TYPE_DESC = 'CCC' THEN 3
WHEN RCVR_TYPE_DESC = 'DDD' THEN 4
end
) = 1

Dieter