aju_k aju_k - 4 months ago 7
MySQL Question

mysql select rows of past 3 days for each customer

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.

Example table:

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


So far I have the following:

SELECT *
FROM table
WHERE timestamp BETWEEN max(timestamp) AND (max(timestamp) - INTERVAL 3 DAY)
GROUP BY cust_ID


But it gives the error:


1111 'invalid use of group function'

Answer

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 BETWEEN, ie

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.

Comments