Written by

Jude Mukkadayil

Question Jude Mukkadayil · May 12, 2021

Cache sql query

#Caché #SQL

Hi ,

I am trying to retrieve the contact type column data of a patient based on the recent date as shown below.

for instance

contacttype Datefrom

contact 1. 24/03/2020

contact 2. 20/05/2021

i need to retrieve the second contact2 based on the recent date.

i tried like below but not working .

Case max(datefrom) >0 then contacttype

End as contacttype

Can anyone please tell me why this is not working.Any other options?

Thanks

jude

Product version: Caché 2015.1

Discussion (3)0

Add reply

Comments

Jon Willeke · May 12, 2021

Are you looking for something like this?

select top 1 contacttype
from table_name
order by datefrom desc

0 0

Jude Mukkadayil May 12, 2021 to Jon Willeke

Yes. Since it’s a long query , I don’t have an option to include a sub query.

Thanks

0 0

Robert Cemper · May 12, 2021

I assume you have a PatientID so you might be able to select your records using an INNER JOIN in this way:

SELECT <.......your columns....,contacttype,....> 
FROM my.patient as pat
INNER JOIN 
(select PatientID, MAX(DateFrom) as MaxDate FROM my.patient Group by PatientID) as max
ON  
pat.PatientID=max.PatientID AND pat.DateFrom=max.MaxDate
WHERE  <...whatever... >

so you get the records with the highest DateFrom by Patient

0 0