Henrique M. Henrique M. - 3 months ago 12
MySQL Question

How can I select the second minimal value within a inner join?

I have this query

select c.id, c.name, c.email, c.totalpets, min(p.date_created) as first_order,
min(p.weight) as min_weight_bought,
max(p.weight) as max_weight_bought,
count(p.ordernumber) as total_orders
from orders p
inner join customers c
on p.customer_id = c.id
where p.approved = 1
and c.totalpets >= 1
group by c.id
having total_orders > 1


Note that first_order gives me the first result of the row, right? I am trying to get customer first order and customer second order. How can i do that within this inner join?

Thanks

Answer
SELECT c1.id,
       c1.name,
       c1.email,
       c1.totalpets,
       p1.date_created
FROM orders p1
INNER JOIN customers c1
    ON p1.customer_id = c1.id
WHERE 
(
    SELECT COUNT(*)
    FROM orders p2
    INNER JOIN customers c2
        ON p2.customer_id = c2.id
    WHERE c2.id = c1.id AND p2.date_created <= p1.date_created
) <= 2 
ORDER BY c1.id;

Here is a running demo which shows a simplified version of the above query (and simplified data set) in action:

SQLFiddle

Comments