CODEWITHSUNDEEP
sqloracle-database
comp_user
comp_user

Reputation: 60

Select the latest client record from query with many joins

I have a query with many joins and I am trying to select the latest client record from it. This is the query I have but it is giving me all records of the client:


EDIT:

SELECT ca.CA_ID, ca.CLIENT_ID, ca.CA_STATUS_TYPE, ca.CREATED_BY, MAX(ca.CREATED_DATE), ca.UPDATED_BY, MAX(ca.UPDATED_DATE), ca.CA_REFERENCE_NUMBER, 
cl.SOURCE_SYSTEM_CODE, catchment.EST_CATCHMENT_AREA,  catchment.SERVICE_PROVIDER_NAME, sds.SDS_NAME, sds.SDS_ADDRESS_TEXT, u.CA_USER_TYPE "USER TYPE",

CASE WHEN ca.REFERRED_IND = 1 THEN 'REFERRED' ELSE 'NOT REFERRED' END REFER_TYPE
FROM CA ca
JOIN(
     SELECT TOP 1
     CLIENT_ID, MAX(CREATED_DATE) CREATED_DATE
     FROM CA
     GROUP BY
     CLIENT_ID
     ORDER BY
     MAX(CREATED_DATE)
     ) maxclient
     ON maxclient.CLIENT_ID = ca.CLIENT_ID
     AND maxclient.CREATED_DATE = ca.CREATED_DATE
LEFT JOIN CLIENT cl 
ON ca.CLIENT_ID = cl.CLIENT_ID 
LEFT JOIN (
    SELECT * FROM CATCHMENT_AREA catchment LEFT JOIN SERVICE_PROVIDER sp ON sp.SERVICE_PROVIDER_ID = catchment.SERVICE_PROVIDER_ID
  ) catchment ON ca.CATCHMENT_ID = catchment.CATCHMENT_ID
LEFT JOIN SERVICE_DELIVERY_SITE sds 
ON ca.SERVICE_DELIVERY_SITE_ID = sds.SERVICE_DELIVERY_SITE_ID 
LEFT JOIN USER u 
ON ca.CREATED_BY = u.CA_USER_ID 
WHERE (ca.CREATED_DATE) BETWEEN TO_DATE('2021-01-04', 'yyyy-mm-dd') AND TO_DATE('2021-01-08', 'yyyy-mm-dd')

Upvotes: 1

Views: 61

Answers (1)

Heitor
Heitor

Reputation: 81

Oracle don't have the top clause:

If you want the max created date from all est_ca_client_id you do:

select est_ca_client_id, max(created_date) created_date
  from ca_cdr.est_ca
  group by est_ca_client_id

But if you want the last est_ca_client_id from the created_date, assuming you don't have clients created in the same second you can do something like:

select est_ca_client_id
  from ca_cdr.est_ca
 where created_date = (select max(created_date)
                         from ca_cdr.est_ca)

Upvotes: 1

Related Questions