select * from [table] where min(date) > certain date

i have a table with customers. every customers has several orders with the paid price and day he/she ordered an item. i want only customers their first order was after a certain date

orders table looks like this:

id | customer | item | price | date
--------------------------------------------
1  |    a      |  a2  |   50  | 2018-07-03 
2  |    b      |  a5  |   30  | 2019-12-06
3  |    c      |  a3  |   20  | 2020-01-14
4  |    a      |  a2  |   23  | 2017-07-12
5  |    f      |  a1  |   34  | 2018-10-03
6  |    c      |  a1  |   90  | 2018-03-03
7  |    b      |  a2  |   56  | 2020-02-03
8  |    a      |  a2  |   52  | 2019-05-03

SELECT customer FROM orders WHERE min(date) > TO_DATE('2018-09-01', 'YYYY-MM-DD')

i want the customer only when the first order was after 2018-09-01, the min(date) > TO_DATE(‘2018-09-01’, ‘YYYY-MM-DD’)

result should be customers: b f

Answer

Use aggregation:

SELECT customer
FROM orders o
GROUP BY customer
HAVING min(date) > DATE '2018-09-01';

You don’t need to convert the date to a string for comparisons. Depending on your database, DATE may not be required.

Advertisement

Answer