CODEWITHSUNDEEP
sqloracle-sqldeveloper
user6522753
user6522753

Reputation:

SQL to retrieve last 3 records if they match a certain criteria. Complicated By Joins On Multiple Tables

[]

Hi Folks,

Looking at the above ERD, Can anyone help me with the following.

I am looking to get the latest 3 records on the CI_MR_REM table as per GEO_VAL on the CI_PREM_GEO table.

So far I have been able to get this part sorted, but I need to further filter the list IF all 3 of the records have a certain value in the field of READER_REM_CD on the CI_MR_REM

This is the SQL I have developed so far. Any ideas?

SELECT * FROM

    (
      SELECT PGEO.GEO_VAL AS GPRN,SPMTRHIS.MTR_CONFIG_ID,MR1.MR_ID,MR1.READ_DTTM,MR1.USE_ON_BILL_SW,MRREM.READER_REM_CD,

      ROW_NUMBER() OVER (PARTITION BY PGEO.GEO_VAL ORDER BY MR1.READ_DTTM DESC) AS READ_NUMBER

      FROM CI_SP SP,CI_SP_MTR_HIST SPMTRHIS,CI_MR MR1,CI_MR_REM MRREM,CI_PREM_GEO PGEO 

      WHERE MR1.MR_ID = MRREM.MR_ID
      AND SPMTRHIS.MTR_CONFIG_ID = MR1.MTR_CONFIG_ID
      AND SP.SP_ID = SPMTRHIS.SP_ID
      AND SP.PREM_ID = PGEO.PREM_ID

      AND PGEO.GEO_TYPE_CD = 'GPRN'
    )
WHERE READ_NUMBER <=3

To clarify, the screenshot below is the results I presently have

I need the results further filtered so that the Highlighted 0954765 is retained as all 3 of its last values is GA, Where as the likes of 0198645, 0638843, 1091524 And 1160618 should not be as their values on the READER_REM_CD column are a mixture.

[]

Upvotes: 0

Views: 55

Answers (1)

Massum Hussain
Massum Hussain

Reputation: 51

write the code like below

WHERE READ_NUMBER between 1 and 3

Upvotes: 0

Related Questions