89ZiNws 89ZiNws - 9 days ago 6
MySQL Question

Mysql: showing order number per customer for each order

I have a table listing order_id, order_date and user_id.

I have no issue retrieving how many orders each customer has made and displaying that next to the order_id. However, what I've been trying unsuccessfully is to display next to each order_id which the n-th order value for each individual customer.

SELECT order.id,
order.user_id,
date(from_unixtime(order.created_date))
COUNT(order2.id)
FROM order order
JOIN order order2
ON order2.user_id = order.user_id
AND order2.created_date <= order.created_date
GROUP
BY order.user_id,
date(from_unixtime(order.created_date)),
order.id
ORDER
BY order.user_id,
date(from_unixtime(order.created_date)),
order.id;


This gives me just the total numbers of orders per user for each of his orders rather than the n-th value.

The result should like something like this:

created_date order_id user_id order_number
2016-11-21 234 12 2
2016-10-15 123 16 1
2016-10-03 234 12 1


many thanks in advance!

Answer

Well, you can add another one join and change the first a bit:

SELECT order.id,
       order.user_id,
       date(from_unixtime(order.created_date))
       COUNT(distinct order3.id) - COUNT(distinct order2.id)
FROM order order
JOIN order order2
           ON order2.user_id = order.user_id
AND order2.created_date > order.created_date
JOIN order order3
           ON order2.user_id = order.user_id
GROUP
BY order.user_id,
   date(from_unixtime(order.created_date)),
   order.id
ORDER
BY order.user_id,
   date(from_unixtime(order.created_date)),
   order.id;
Comments