Henrique M. Henrique M. - 8 months ago 35
MySQL Question

In Mysql, how to select two highest values and compare date between then

Let's suppose I have two tables:


Orders have these columns:


Customers have these columns:


I am trying to build a query to show me the average range of the latest customer orders, for example:

  • get two latest orders from all customers (select only customers that have more than 1 order, of course)

  • compare the dates and retrieve the average time from o1 to o2 (all customers)

I am not very experienced with MySQL, but so far I have managed to get the max value.

select max(o.order_created), c.id, c.name, c.email,
(date(max(o.order_created)) - date(min(o.order_created))) as date_interval
from orders o
inner join customers c
on c.id = o.customer_id
group by c.id
having date_interval > 0

doesn`t work, of course, because I need to retrieve the second max value, not the minimum. And after all that, it needs to retrieve an average value for all this query, which I have no clue how to build.

How can I approach this?

Thanks everybody.

I don`t think the duplicate mark solve my question

vkp vkp

One way to do it is to calculate the row numbers where the latest order per customer gets row number 1 and then calculate the date difference between the latest and the next latest order (for those customers who have atleast 2 orders)

select t1.customer_id,
datediff(max(case when rownum=1 then order_date end) ,
         max(case when rownum=2 then order_date end)
        )/2.0 diff
from (select o.*,
      @rn:=if(@previous=customer_id,@rn,0) + 1 as rownum,
      from orders o, (select @rn:= 0, @previous:= null) t
      order by customer_id,order_date desc) t1
join (select customer_id from orders group by customer_id having count(*) > 1) t2 
on t1.customer_id=t2.customer_id
group by t1.customer_id