CODEWITHSUNDEEP
sqloracle-databasedistinctrank
caroline
caroline

Reputation: 13

dense rank duplicate values oracle

So I am really happy being able to rank results based on effective dates, but currently I'm having an issue where one data element repeats (POD) while another changes based on EFFDT (DEPT).

I only want to rank unique values for Pod, and later Dept. However Pod is based on Dept, which changes more frequently. The below code gives me:

EENBR  PodRank POD DeptRank DeptNbr   DeptEffdt
100     1       73    1    12420      4/11/2005
100     2       73    2    12560      5/22/2005
100     3       73    3    12501      6/24/2007
200     1       12    1    50768      3/14/2005
200     2       13    2    10949      9/9/2012
300     1       73    1    12450      3/21/2005
300     2       73    2    12471      12/25/2005
300     3       73    3    12581      12/21/2008
300     4       73    4    12585      6/6/2010
300     5       73    5    12432      5/19/2013



SELECT DISTINCT 
       AL4.FULL_NAME,
       AL4.EMPLOYEE_NUMBER,
       dense_rank() over (partition by AL4.EMPLOYEE_NUMBER
                          order by AL3.EFFECTIVE_START_DATE) as POD_RANKING,
       AL7.POD_NBR as POD,
       row_number() over (partition by AL4.EMPLOYEE_NUMBER
                          order by AL3.EFFECTIVE_START_DATE) as DEPT_RANKING,
       AL3.RECORDVALUE AS DEPT_NUMBER,
       AL3.EFFECTIVE_START_DATE AS "DEPT EFFECTIVE DATE"     
  FROM T1 AL3,
       T2 AL4,
       T3 AL7 
 WHERE AL4.PERSON_ID = AL3.PERSON_ID
   AND AL4.EMPLOYEE_NUMBER = AL3.EMPLOYEE_NUMBER
   AND AL3.RECORDTYPE = 'DEPARTMENT_NUMBER'
   AND AL7.DEPT_NBR = AL3.RECORDVALUE
 Order By AL4.Employee_Number;

Is there a function that only ranks unique values?

Upvotes: 1

Views: 5957

Answers (2)

dnoeth
dnoeth

Reputation: 60462

There's no function for this, but you can get the result when you use nested window functions:

SELECT dt.*,
   SUM(flag) OVER (PARTITION BY EMPLOYEE_NUMBER
                   ORDER BY "DEPT EFFECTIVE DATE") AS POD_RANKING
FROM
 ( 
   SELECT
          AL4.FULL_NAME,
          AL4.EMPLOYEE_NUMBER,
          AL7.POD_NBR AS POD,
          ROW_NUMBER() OVER (PARTITION BY AL4.EMPLOYEE_NUMBER
                             ORDER BY AL3.EFFECTIVE_START_DATE) AS DEPT_RANKING,
          AL3.RECORDVALUE AS DEPT_NUMBER,
          AL3.EFFECTIVE_START_DATE AS "DEPT EFFECTIVE DATE",  
          CASE WHEN ROW_NUMBER() 
                    OVER (PARTITION BY AL4.EMPLOYEE_NUMBER,AL7.POD_NBR
                          ORDER BY AL3.EFFECTIVE_START_DATE) = 1 THEN 1 ELSE 0 END AS flag
     FROM T1 AL3,
          T2 AL4,
          T3 AL7 
    WHERE AL4.PERSON_ID = AL3.PERSON_ID
      AND AL4.EMPLOYEE_NUMBER = AL3.EMPLOYEE_NUMBER
      AND AL3.RECORDTYPE = 'DEPARTMENT_NUMBER'
      AND AL7.DEPT_NBR = AL3.RECORDVALUE
  ) dt
ORDER BY AL4.Employee_Number;

Edit: Ok, I noticed this is a overly complex version of a simple DENSE_RANK with different order, shortly before Gordon posted his answer :-) 

dense_rank() over (partition by AL4.EMPLOYEE_NUMBER order by AL7.POD_NBR)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The function you are looking for is the analytic function dense_rank():

dense_rank() over (partition by eenbr order by pod) as ranking

This is the simplest way to get what you want. You can just add it in the select clause of your query.

Upvotes: 2

Related Questions