Question 248501 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Select MAX days passed between two records that match some criteria

Let’s assume below table orders_daily_data in which we store number of orders made for a given product across the time:

ID ProductType      Orders     OrderDate    
1   sugar           5          2023-01-04   
2   flour           0          2023-01-04   
3   pepper          7          2023-01-04
4   sugar           1          2023-01-05
5   flour           32         2023-01-05
6   pepper          0          2023-01-05
7   sugar           null       2023-01-06
8   pepper          1          2023-01-06
9   flour           0          2023-01-07
10  flour           0          2023-01-10
11  sugar           1          2023-01-11
12  pepper          1          2023-01-19
13  flour           1          2023-01-19

I’d like to find out the maximum number of days between two points in time when product was ordered.

Desired result based on above data set:

sugar was not ordered for a longest time between 2023-01-05 and 2023-01-11 => 6 days

flour was not ordered for a longest time between 2023-01-05 and 2023-01-19 => 14 days

pepper was not ordered for a longest time between 2023-01-06 and 2023-01-19 => 13 days

Notes:

  • There may be gaps in this table, meaning products were not ordered during that time
  • null is treated as 0

I’ve tried bunch of approaches with join & group by but nothing seem to work as I expect:

select MAX(DATEDIFF(higher.OrderDate, lower.OrderDate)) from
orders_daily_data higher join orders_daily_data lower
on higher.ProductType = lower.ProductType and lower.OrderDate < higher.OrderDate
where higher.Orders > 0 and lower.Orders > 0
group by lower.ProductType

I would appreciate any help 🙂

Tags:

3

Answers


  1. 0

    Try this:

    SELECT ProductType, Max(Days) Days
FROM (
    select ProductType,
        datediff(OrderDate, lag(OrderDate) over (partition by ProductType order by OrderDate)) Days
    from orders_daily_data
    where coalesce(Orders,0) <> 0
) t
GROUP BY ProductType

    https://dbfiddle.uk/PoGPd_2I

    Results:

    ProductType Days
    flour 14
    pepper 13
    sugar 6

    The JOIN approach in the original question was close, but you needed the join to match only the one most recent row for each source row, and the easiest way to do that is with a LATERAL JOIN. You could also find the date you need to subtract with a correlated subquery in the SELECT clause…

    …but the windowing function approach I demonstrated above is typically the fastest/most efficient method, and with the least code, too.

    Login or Signup to reply.
  2. 0

    One way you can approach this, among several, particularly if your MySql is < v8 is to use a correlated sub-query:

    select ProductType, Max(days) Days
from (
  select ProductType, DateDiff(
    Orderdate, (
      select max(orderdate) from orders_daily_data d2 
      where d2.ProductType = d.producttype and d2.orderdate < d.orderdate and Orders > 0
      )
    ) days
  from orders_daily_data d
)t
group by ProductType
order by Days desc;
    Login or Signup to reply.
  3. 0

    Going off of the answer by @Joel Coehoorn; if you don’t have mysql 8, this is closer to the join example that the OP was attempting. I would recommend adding an index as well to speed things up, if possible.

    https://dbfiddle.uk/lbDT8AjB

    Results:

    ProductType LastOrderDate PreviousOrderDate Days
    sugar 2023-01-11 00:00:00 2023-01-05 00:00:00 6
    pepper 2023-01-19 00:00:00 2023-01-06 00:00:00 13
    flour 2023-01-19 00:00:00 2023-01-05 00:00:00 14
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search