SQL MAX in WHERE

Question

select contract.id, invoice.period_to
from public.invoice
right join public.contract on invoice.contract_id = contract.id
where (MAX(invoice.period_to) <= '2017-10-01' OR invoice.id IS NULL)
       ^^^^^^^^^^^^^^^^^^^^^ - I need help here
AND contract.close_type IS NULL 
    AND contract.payment_type != 'TRIAL' 
    group by contract.id;

I know i cant use MAX under WHERE so im try doing something like

where (invoice.period_to IN(SELECT MAX(invoice.period_to) FROM public.invoice GROUP BY invoice.period_to ) <= '2017-10-01' OR invoice.id IS NULL)

But error ERROR: invalid value for logical type: "2017-10-01"

Help me pls

aggregate filters should be placed in a `having` clause, not the `where` clause. — Igor, Oct 23 '17 at 16:09
Possible duplicate of [Sql Server : How to use an aggregate function like MAX in a WHERE clause](https://stackoverflow.com/questions/1475589/sql-server-how-to-use-an-aggregate-function-like-max-in-a-where-clause) — underscore_d, Oct 23 '17 at 16:10
Possible duplicate of [SQL - WHERE AGGREGATE>1](https://stackoverflow.com/questions/1213004/sql-where-aggregate1) — Igor, Oct 23 '17 at 16:10
What is the datatype of `invoice.period_to`? Actually why do you need `MAX()` when you're checking for `<= '2017-10-01'`? — SS_DBA, Oct 23 '17 at 16:13

Gordon Linoff · Accepted Answer · 2017-10-23T16:22:17.653

2

Presumably you want:

select c.id, max(i.period_to)
from public.contract c left join
     public.invoice
     on i.contract_id = c.id
where c.close_type IS NULL and c.payment_type <> 'TRIAL' 
group by c.id
having max(i.period_to) <= '2017-10-01' OR max(i.id) IS NULL;

edited Oct 23 '17 at 16:22

answered Oct 23 '17 at 16:16

Gordon Linoff

1,242,037
58
646
786

Thx. That is what i want. One little thing that confused me is: OR max(i.id) IS NULL, but i think without MAX it didnt work under HAVING – Anton Kolosok Oct 24 '17 at 06:57
1

@AntonKolosok You need to specify which aggregate you want to use in `having`, because it performs aggregate filtering, but it's not restricted to using the same aggregate(s) that you already used in the `select` clause. For example, you might want to group by month, then select the latest (`max`) date in each group, but only where the earliest (`min`) date in each group is before or after some threshold, or etc. – underscore_d Oct 24 '17 at 08:58

SQL MAX in WHERE

1 Answers1