CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Sly
Sly

Reputation: 73

Rank() over(partition by oracle returning 1 record

I have a requirement to pull up job titles that each candidate was hired into, because there are cases where a candidate can be hired and then the hire canceled, i am using the rank function to make sure i only pick up the final job we hired someone into. The problem is my query is returning only one record when i am sure i have more than one record. Here is my oracle code i am using. 

SELECT E.PI_CANDIDATE_NUM,
       J.TITLE,
       RANK() OVER(PARTITION BY E.PI_CANDIDATE_NUM
          ORDER BY E.PI_HIS_ITM_APP_TRK_CREATION_DT DESC) AS ROWRANK
  FROM WC_HIRED_INTERNS_FA H
       INNER JOIN WC_APPLICATION_EVENT_F E
        ON H.PI_CANDIDATE_NUM = E.PI_CANDIDATE_NUM
       INNER JOIN WC_JOB_INFORMATION_D J
        ON E.JOB_INFO_ROW_WID = J.ROW_WID
 WHERE     ROWNUM = 1
       AND J.JOB_FAMILY_NAME IN ('M', 'P', 'E', 'O')
       AND E.PI_HIS_ITM_APP_TRK_STS_NAME = 'Extended'
       AND E.PI_HIS_ITM_APP_TRK_STEP_NAME = 'Offer'
       AND J.TITLE NOT IN ('Student Ambassador');

Upvotes: 0

Views: 2303

Answers (1)

Aleksej
Aleksej

Reputation: 22949

You seem to need:

SELECT *
FROM 
(
    SELECT E.PI_CANDIDATE_NUM,
           J.TITLE,
           RANK() OVER(PARTITION BY E.PI_CANDIDATE_NUM
              ORDER BY E.PI_HIS_ITM_APP_TRK_CREATION_DT DESC) AS ROWRANK
      FROM WC_HIRED_INTERNS_FA H
           INNER JOIN WC_APPLICATION_EVENT_F E
            ON H.PI_CANDIDATE_NUM = E.PI_CANDIDATE_NUM
           INNER JOIN WC_JOB_INFORMATION_D J
            ON E.JOB_INFO_ROW_WID = J.ROW_WID
     WHERE  -- no conditions on ROWNUM
               J.JOB_FAMILY_NAME IN ('M', 'P', 'E', 'O')
           AND E.PI_HIS_ITM_APP_TRK_STS_NAME = 'Extended'
           AND E.PI_HIS_ITM_APP_TRK_STEP_NAME = 'Offer'
           AND J.TITLE NOT IN ('Student Ambassador')
)
WHERE ROWRANK = 1

Upvotes: 4

Related Questions