Henrique M. Henrique M. - 10 months ago 43
MySQL Question

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

Let's suppose I have two tables:

customers
and
orders


Orders have these columns:

order_created
customer_id


Customers have these columns:

id
name
email


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


min
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.

@Edit
I don`t think the duplicate mark solve my question

vkp vkp
Answer Source

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,
      @previous:=customer_id
      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