I have a table like
x
create table appointments
(
id serial not null constraint appointments_pkey primary key,
patient_id varchar(255) not null,
date timestamp(0) not null
);
I want to get date of next appointment and previous appointment in single query.
I created query as
SELECT
(
SELECT MIN(ap.date) FROM appointments ap
WHERE ap.date >= NOW() AND ap.patient_id = '1'
) AS next,
(
SELECT MAX(ap.date) FROM appointments ap
WHERE ap.date < NOW() AND ap.patient_id = '1'
) AS last
which works as expected, but I think it is not optimized because two subqueries. Can you help me to optimize me such query,please?
Advertisement
Answer
you can use case when expression
select
min(case when ap.date >= NOW() then ap.date end) next,
max(case when ap.date < NOW() then ap.date end) last
from appointments ap
where ap.patient_id = '1'