CODEWITHSUNDEEP
sqloracle-database
Craig C
Craig C

Reputation: 165

How To Rank In Oracle SQL Per Group For Whole Table?

I am using Oracle SQL Developer to attempt to automatically calculate periods of absence from an HR table. This table has the following relevant fields: Person_Ref - Unique person identifier START_DATE - The date that a period of absence commenced. COMPDATE - Seems to be a 6-digit number which counts downwards, so lowest number is most recent. I ultimately wish to write a statement that will rank the absence start dates for every person, take the newest START_DATE (i.e. lowest COMPDATE), and the 3rd newest START_DATE, then mathematically calculate the number of days between them (n.b. this table contains decades of absence, each entry indexed against PERSON_REF). This is a business requirement to report on cases where there are more than 3 periods of absence in 12 months (i.e. have the start dates of the last 3 periods of absence all fallen within 12 months).

I intend to firstly extract PERSON_REF, newest START_DATE, 3rd newest START_DATE, and COMPDATE into a separate table, hopefully with one line per PERSON_REF, then do the rest of the logic in Visual Studio. However this is where my SQL knowledge lets me down.

I can easily get ranked dates returned if I run this statement for a specific PERSON_REF:

SELECT D57.PERSON_REF, D57.START_DATE, D57.COMPDATE, RANK() OVER (ORDER BY D57.COMPDATE asc) rnk
From D57
WHERE D57.PERSON_REF='050050713';

From there I can pick the 1st & 3rd dates from the results in the rest of my query.

However, if I removed the WHERE command, then obviously it just ranks the entire table in order.

What I really want to know is, how can I write this sub query to return ranked results per PERSON_REF for every PERSON_REF in the table?

Upvotes: 1

Views: 6297

Answers (1)

podiluska
podiluska

Reputation: 51494

Use the Partition by clause

SELECT 
    D57.PERSON_REF, D57.START_DATE, D57.COMPDATE, 
    RANK() OVER (PARTITION BY PERSON_REF ORDER BY D57.COMPDATE asc) rnk 
FROM D57

Upvotes: 4

Related Questions