CODEWITHSUNDEEP
sqloracle-database
nanquim
nanquim

Reputation: 1924

Oracle - Select where max date

I need to select only customers that don't have a ticket with competence date '01/01/2019', I mean the last ticket was from '01/12/2018' to '31/12/2018'

I cannot use max as 'where max(date) = '

How can I do this?

TICKET TABLE:

CUSTUMER ID | TICKET NUMBER | COMPETENCE_DATE

I need to get the custumers that DON'T have a ticket for january, I mean, their last ticket was from 2018

Upvotes: 1

Views: 1430

Answers (2)

dougp
dougp

Reputation: 3089

HAVING

select [CUSTOMER ID]

from TICKET

group by [CUSTOMER ID]

having max(COMPETENCE_DATE) < {d '2019-01-01'}
    or max(COMPETENCE_DATE) is null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If you want customers whose maximum date is in a particular range, use group by and having:

select customerid
from t
group by customerid
having max(competence_date) >= date '2018-12-01' and
       max(competence_date) < date '2019-01-01';

Upvotes: 2

Related Questions