Help with oracle query

See more:

Oracle

Hi all, I have this Oracle query and need to add a column ( MyValue ) from another table ( Entitlements ) based on the largest value found in a column called ent_refno , this table has a common key in Claim_Periods called cpe_refno, any ideas how to achieve this ?


Current query

SELECT distinct clms.CLA_REFNO
  FROM CLAIMS clms
  inner join CLAIM_PARTS cpart
  on clms.CLA_REFNO = cpart.CPA_CLA_REFNO
  inner join CLAIM_PERIODS cperiods
  on (cpart.CPA_CLA_REFNO = cperiods.CPE_CPA_CLA_REFNO
  And cpart.CPA_CPY_CODE = cperiods.CPE_CPA_CPY_CODE)
  WHERE ( FQV_CURR_CL_FLAG(clms.CLA_REFNO) = 'Y' )
  AND ( SYSDATE between cperiods.CPE_START_DATE AND cperiods.CPE_END_DATE)
  AND ( s_claim_periods.latest_tenancy_code(clms.CLA_REFNO) IN ( 'PTEN' )
  AND s_claim_periods.latest_tenure_code(clms.CLA_REFNO)    IN ( 'LHA' )
  AND cpart.CPA_CPY_CODE IN ('HB')
  AND cperiods.CPE_CPS_CODE IN ( 'A','R' ) )

The table Entitlements ( cut down for brevity )

ent_refno cpe_refno MyValue
1            22      100.99
2            22      100.92 
3            22      400.88
4            22      209.76
5            23      209.76
6            23      212.44
7            24      111.22
8            25      256.33
9            25      222.33

In this example the MyValue for cpe_refno = 22 would be 209.76
In this example the MyValue for cpe_refno = 25 would be 222.33

What I have tried:

Googling , the usual heavy drinking and cursing