ORACLE SQL Returning most recently added record detail

Edit: Solved with a lot of help from Dems, I will post a truncated version of the query which is now working very well!

            SELECT
  *

FROM

    O_PERSONS

    /*The below left join returns the most recently added ethnicity classification*/
  LEFT JOIN 

  (SELECT
   ROW_NUMBER() OVER (PARTITION BY O_CLASSIFICATIONS.CLA_SUBJECT_ID ORDER BY O_CLASSIFICATIONS.CLA_DATE_NOTIFIED DESC) AS Sequence_ID,
   O_CLASSIFICATIONS.CLA_CAT_ID,
   O_CLASSIFICATIONS.CLA_SUBJECT_ID
   FROM
   O_CLASSIFICATIONS
   WHERE
   O_CLASSIFICATIONS.CLA_SUBJECT_IND = 'P'
   AND
   O_CLASSIFICATIONS.CLA_TOP_CAT_ID   =  'ETHNIC'
   AND
   O_CLASSIFICATIONS.CLA_CAT_ID <> 'DECLINED'
   ) ETHNIC 
   ON ETHNIC.CLA_SUBJECT_ID = O_PERSONS.PER_ID 
   AND ETHNIC.Sequence_ID = 1

    /*The below left join returns the most recently added PCG classification*/
   LEFT JOIN 

  (SELECT
   ROW_NUMBER() OVER (PARTITION BY O_CLASSIFICATIONS.CLA_SUBJECT_ID ORDER BY O_CLASSIFICATIONS.CLA_DATE_NOTIFIED DESC) AS Sequence_ID,
   O_CLASSIFICATIONS.CLA_CAT_ID,
   O_CLASSIFICATIONS.CLA_SUBJECT_ID
   FROM
   O_CLASSIFICATIONS
   WHERE
   O_CLASSIFICATIONS.CLA_SUBJECT_IND = 'P'
   AND
   O_CLASSIFICATIONS.CLA_TOP_CAT_ID   =  'PRIMARY'
   AND
   O_CLASSIFICATIONS.CLA_CAT_ID <> 'DECLINED'
   ) PCG
   ON PCG.CLA_SUBJECT_ID = O_PERSONS.PER_ID 
   AND PCG.Sequence_ID = 1


  WHERE

   O_PERSONS.PER_ID LIKE 'P%'

I am currently trying to write a query which will return certain details for clients on our system. Whilst a client will only have one date of birth, gender, and P ID, they may have multiple ethnicities (as a result of data quality) and also client groups (this is more legitimate as a client's needs may change). However, for these particular items, I am only interested in the most recently added ethnicities or client groups. Below I have written a query which assigns a row number to the ethnicity and to the client group with a plan to return only those which equal 1, as these generally will be the most recent. However, I have run into 2 issues, one of which I can resolve, the other of which I'm not so sure.

First off, some clients will not have an ethnicity or a client group recorded. When running the below query, they return with very odd row numbers (often in the thousands). However, I know in my 'where' that I could specify that a row is returned if client group or ethnicity was empty (as these will be data quality cases which need to be addressed).

The second issue is slightly trickier which I will try to represent with a table below. Please note I have abbreviated the table for ease of input and understanding.

  P ID Ethnicity   PCG RN E RN P
  P1   WB          OV  1    2 
  P1   WI          OV  2    1          

The above client had two ethnicities, though WB is the most recent (therefore the 1 in Row Number E is correct). However, the client has only had one PCG recorded but the row number returns a 2 in the first row (arguably the row I want to return). I'm not sure why though I guess because ETHNIC.CLA_SUBJECT_ID is joined on O_PERSONS.PER_ID and PER_ID occurs twice, that is why it thinks there is two rows for that particular field. However, even if that is the case, is there a way to force just 1's to appear in one row? Or could I do this a completely different way? Hopefully this query makes some sense, apologies if parts are unclear. Thanks,

                         SELECT
          O_PERSONS.PER_ID as "P ID",
          olm_bo.get_per_name(O_PERSONS.PER_ID) as "Full Name",
          O_PERSONS.PER_BIRTH_DATE as "Date of Birth",
          case 
            when O_PERSONS.PER_DECEASED_DATE is null then FLOOR(MONTHS_BETWEEN(sysdate,O_PERSONS.PER_BIRTH_DATE)/12) 
            else FLOOR(MONTHS_BETWEEN(O_PERSONS.PER_DECEASED_DATE,O_PERSONS.PER_BIRTH_DATE)/12) 
          end as "Age",
          O_PERSONS.PER_DECEASED_DATE as "Date Deceased",
          olm_bo.get_gender_desc('P', O_PERSONS.PER_GENDER) as "Gender",
          CASE 
            WHEN ETHNIC.CLA_CAT_ID IN ('C1','C2','C3','C4','ABAN','AIND','AOTH','APKN') THEN 'Asian or Asian British'
            ELSE NULL
          End as "Ethnicity - Top" ,
          CASE 
            WHEN ETHNIC.CLA_CAT_ID IN ('BAFR','D2') THEN 'African'
            ELSE NULL
          End as "Ethnicity - Detail" ,
          CASE 
            WHEN PCG.CLA_CAT_ID IN ('ASYLUM','REFUGEE') THEN 'Asylum Seeker/Refugee'
            ELSE NULL
          End as "PCG",
          CASE 
            WHEN PCG.CLA_CAT_ID IN ('ASYLUM','REFUGEE') THEN 'Asylum Seeker/Refugee'
            ELSE NULL
          End as "PCG - Top",
          CASE 
            WHEN PCG.CLA_CAT_ID IN ('ASYLUM','REFUGEE') THEN 'Asylum Seeker/Refugee'
            ELSE NULL
          End as "PCG - DETAIL",

          to_char(row_number() over(PARTITION BY  ETHNIC.CLA_SUBJECT_ID ORDER BY abs(sysdate - ETHNIC.CLA_DATE_NOTIFIED) asc)) as "Row Number E",
          to_char(row_number() over(PARTITION BY  PCG.CLA_SUBJECT_ID ORDER BY abs(sysdate - PCG.CLA_DATE_NOTIFIED) asc))as "Row Number P"

        FROM

          O_PERSONS

          LEFT JOIN O_CLASSIFICATIONS ETHNIC ON ETHNIC.CLA_SUBJECT_ID = O_PERSONS.PER_ID 
          AND ETHNIC.CLA_SUBJECT_IND = 'P'
          AND ETHNIC.CLA_TOP_CAT_ID   =  'ETHNIC'
          AND ETHNIC.CLA_CAT_ID <> 'DECLINED'

          LEFT JOIN O_CLASSIFICATIONS PCG ON PCG.CLA_SUBJECT_ID = O_PERSONS.PER_ID 
          AND PCG.CLA_SUBJECT_IND = 'P'
          AND PCG.CLA_TOP_CAT_ID   =  'PRIMARY'
          AND PCG.CLA_CAT_ID <> 'DECLINED'

          WHERE
          /*Following line excludes any clients whose is less than 18)*/
            O_PERSONS.PER_BIRTH_DATE > trunc(add_months(O_PERSONS.PER_BIRTH_DATE,-216))
            AND  O_PERSONS.PER_ID LIKE 'P%'

Answers


You should apply ROW_NUMBER() before you do the joins.

SELECT
  *     -- Your calculations here, '*' used for brevity
FROM
  O_PERSONS
LEFT JOIN
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY CLA_SUBJECT_ID ORDER BY CLA_DATE_NOTIFIED DESC) AS sequence_id,
    *
  FROM
    O_CLASSIFICATIONS
  WHERE
        CLA_SUBJECT_IND =  'P'
    AND CLA_TOP_CAT_ID  =  'ETHNIC'
    AND CLA_CAT_ID      <> 'DECLINED'
)
  ETHNIC
    ON  ETHNIC.CLA_SUBJECT_ID = O_PERSONS.PER_ID
    AND ETHNIC.sequence_id    = 1
LEFT JOIN
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY CLA_SUBJECT_ID ORDER BY CLA_DATE_NOTIFIED DESC) AS sequence_id,
    *
  FROM
    O_CLASSIFICATIONS
  WHERE
        CLA_SUBJECT_IND =  'P'
    AND CLA_TOP_CAT_ID  =  'PRIMARY'
    AND CLA_CAT_ID      <> 'DECLINED'
)
  PCG
    ON  PCG.CLA_SUBJECT_ID = O_PERSONS.PER_ID
    AND PCG.sequence_id    = 1
WHERE
     /*Following line excludes any clients whose is less than 18)*/
     O_PERSONS.PER_BIRTH_DATE > trunc(add_months(O_PERSONS.PER_BIRTH_DATE,-216))
AND  O_PERSONS.PER_ID LIKE 'P%'

Need Your Help

Can not play a mp4 video in android VIdeoview from a remote url

android video android-videoview dailymotion-api

I have been trying to play a mp4 video from a remote url/dailymotion .I have played an other video in .3gp format from an other website ,it worked fine ,can not play any video from daily motion in my

Update application badge once a day

iphone objective-c ios nsdate

This is the code I have and I it puts the date in a application badge. I was wondering how to reload this number everyday without entering the app to reload it. Thanks in advance!