I would like to select in MySQL the rows of the past three days for each customer based on their maximum timestamp, yet am not sure how to do this.
id | timestamp | cust_ID
899900 | 2016-04-09 12:00:00 | 500219
899901 | 2016-04-12 16:00:00 | 500219
899902 | 2016-04-14 11:00:00 | 500219
899903 | 2016-04-15 12:00:00 | 500219
909901 | 2016-04-08 16:00:00 | 500230
909902 | 2016-04-14 11:00:00 | 500230
909903 | 2016-04-15 12:00:00 | 500230
939905 | 2016-05-02 19:00:00 | 500240
WHERE timestamp BETWEEN max(timestamp) AND (max(timestamp) - INTERVAL 3 DAY)
GROUP BY cust_ID
1111 'invalid use of group function'
Join the table to an aggregation of itself that finds the max timestamp for each customer:
SELECT t.* FROM mytable t JOIN (SELECT cust_ID, MAX(timestamp) max_timestamp -- each cust_ID's max timestamp FROM mytable GROUP BY cust_ID) m ON timestamp BETWEEN max_timestamp - INTERVAL 3 DAY AND max_timestamp AND t.cust_ID = m.cust_ID -- join on the specific cust_ID's max timestamp
Note also that you must have the smaller expression first in a
WHERE myColumn BETWEEN smaller AND larger
If you code it the other way around:
WHERE myColumn BETWEEN larger AND smaller -- don't do this
it will still compile and execute, but you won't get any rows returned.