CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Mr.Smith
Mr.Smith

Reputation: 37

Select where MAX(DATE) between X and X

I'm trying to select only patients, who had their LAST appointment in the year 2015. The ideal thing would look like this:

Select person.name, person.surname
inner join patient on patient.pat_id=person.per_id
inner join appointment on appointment.pat_id=patient.pat_id
where MAX(appointment.date) between '31.12.2014'and '01.01.2016'

But of MAX isn't allowed in WHERE clause. What's the best workaround?

Upvotes: 1

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You are close:

Select p.name, p.surname
from person p inner join
     patient pa
     on pa.pat_id = p.per_id join
     appointment a
     on a.pat_id = pa.pat_id
group by p.per_id, p.name, p.surname
having max(a.date) >= date '2015-01-01' and
      max(a.date) < date '2016-01-01';

Notes:

  • You need group by.
  • Note that I'm including per_id in the group by, because different people can have the same name.
  • Date constants can be introduced with the date keyword.
  • Do not use between with dates; this is particularly true in Oracle where a date column can have a time component.

Upvotes: 2

Related Questions