CODEWITHSUNDEEP
sqlsql-serverdense-rank
Donna Greene
Donna Greene

Reputation: 11

SQL dense_rank function

This is an sql dense_rank function question. I have been researching the internet trying to find an answer. I have tried several versions , but nothing is working. This is the problem that I have: Someone changed a program on 7/13. Effective 7/14, the program now wipes out the salary field. I must find the most current record that is = 0, and then the 2nd most current record where it is > 0. This is one of the codes I have tried:

(SELECT contract_nbr, 
        business_unit, 
        ymdeff, 
        ymdend, 
        ymdtrans, 
        void, 
        salary, 
        Dense_rank() 
          OVER ( 
            partition BY contract_nbr 
            ORDER BY ymdend, salary DESC) 
 FROM   (SELECT DISTINCT contract_nbr, 
                         business_unit, 
                         ymdeff, 
                         ymdend, 
                         ymdtrans, 
                         void, 
                         salary 
         FROM   contract_span 
         WHERE  business_unit = 'KA' 
                AND ymdtrans > 20130714 
                AND contract_nbr = 'XXXX'))

These are the results. They look good except that I just need the bottom 2 lines. Is there a way to just receive the last 2 lines? YMDEND is not always 99991231 so I cannot use that field.

CONTRACT_NBR    BUSINESS_UNIT   YMDEFF  YMDEND  YMDTRANS    VOID    SALARY
XXXX                  KA      20130101  20130531    20130725    V   129300
XXXX                  KA      20130101  20130531    20130725        0
XXXX                  KA      20130601  99991231    20130725    V   129300
XXXX                  KA      20130601  99991231    20130725        0

Upvotes: 1

Views: 401

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I don't think you want dense_rank() at all. Instead, partition on each contract by whether or not salary is 0. Then use row_number() to enumerate the values and logic to choose which ones you want:

from (SELECT contract_nbr, business_unit, ymdeff, ymdend, ymdtrans, void, salary,
             row_number() over (partition by contract_nbr,
                                             (case when salary = 0 then '0' else '+' end)
                                order by ymdend desc
                               ) as SalarySeqNum
      . . .
     ) t
where (salary = 0 and salarySeqNum = 1) or
      (salary > 0 and salarySeqNum = 2)

Upvotes: 1

Related Questions