All Forums Database
TcognosD 11 posts Joined 04/13
10 Jul 2013
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.

dnoeth 4628 posts Joined 11/04
11 Jul 2013

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

Dieter

You must sign in to leave a comment.