Min and max with conditions in single sql query

I have a table like

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?

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'

Advertisement

Answer