MySQL select top n records per group nested

Question

I am trying to get top n (2 for now) rows from MySQL (Version: 5.6.29 and 10.1.28-MariaDB). I have seen simple examples on SO or elsewhere (example1,example2) The problem is that my query is more complex and it doesn't seem to be working well.

I have tables as seen on the diagram (some fields are removed for brevity):

Each purchase_item can be either coupon, purchase_offer or subscription_plan (one is valid and the other two are null). Each of these have subscription_days > 0.

Innermost SELECT works as expected by itself. Second SELECT doesn't work as expected (even if I remove the outer select), purchase_rank is often the same for the same customer_uuid as if the innermost select doesn't sort by customer_uuid.

Any ideas? I read that ORDER BY doesn't work well in nested query perhaps that is the problem in my case? How should I do this then? Or maybe query optimizer is doing something weird? I tried replacing CASE with IF and the result it the same. Here is the code:

SELECT 
    id,
    uuid,
    purchase_offer,
    subscription_plan,
    coupon,
    customer_uuid,
    payment_date,
    subscription_days,
FROM
    (SELECT 
        id,
            uuid,
            purchase_offer,
            subscription_plan,
            coupon,
            customer_uuid,
            payment_date,
            subscription_days,
            @purchase_rank := CASE @current_customer
                WHEN customer_uuid THEN @purchase_rank + 1
                ELSE 1
            END AS purchase_rank,
            @current_customer:= customer_uuid AS current_customer
    FROM
        (SELECT 
            pi.id,
            pi.uuid,
            pi.purchase_offer,
            pi.subscription_plan,
            pi.coupon,
            pi.customer_uuid,
            p.payment_date,
            IFNULL(po.subscription_days, IFNULL(sp.subscription_days, cpo.subscription_days)) AS subscription_days
    FROM
        purchase_item pi
    JOIN purchase p ON p.id = pi.purchase
    LEFT JOIN purchase_offer po ON pi.purchase_offer = po.id
    LEFT JOIN subscription_plan sp ON pi.subscription_plan = sp.id
    LEFT JOIN coupon cp ON pi.coupon = cp.id
    LEFT JOIN purchase_offer cpo ON cp.purchase_offer = cpo.id
    WHERE
        p.status = 'COMPLETED'
            AND pi.customer_uuid IS NOT NULL
            AND p.payment_date IS NOT NULL
            AND (po.subscription_days > 0
            OR sp.subscription_days > 0
            OR cpo.subscription_days > 0)
    ORDER BY pi.customer_uuid , p.payment_date DESC) AS temp) 
AS pu
WHERE
    pu.purchase_rank <= 2
ORDER BY pu.customer_uuid , pu.payment_date DESC

Any help would be greatly appreciated. Thanks in advance.

Cataklysim · Answer 1 · 2017-11-03T08:51:46.220

0

For this kind of limitation of MySql Queries you could simply add a LIMIT 0, 2 at the end of your statement.

For detailed information about the limit statement, feel free to check this documentation with realtime example.

EDIT I

For example: Get the first n results from a statement:

SELECT * FROM purchase
ORDER BY payment_date ASC
LIMIT n

And the other way around:

SELECT * FROM purchase
ORDER BY payment_date DESC
LIMIT n

If you know you want to skip the first n results of your query, you can start LIMITat a given offset. As example, we want to skip the first 10 results:

SELECT * FROM purchase
ORDER BY payment_date DESC
LIMIT 10, n

edited Nov 03 '17 at 08:51

answered Nov 02 '17 at 15:24

Cataklysim

637
6
21

Thank you, but that won't really do. If I use `LIMIT` it will limit all records, not last 2 records per group (`customer_uuid`). Unless I am misunderstanding something? – yoyo-san Nov 03 '17 at 08:25
@RokMirt Last 2? I thought you need the first n results of your group? But eitherway that shouldn't be a problem, since you can choose where to start the limit, you just need to know how many results there are, or work with another `ORDER BY`. – Cataklysim Nov 03 '17 at 08:39
@Cataklysim Sorry, yes I meant first N. However, if I do it your way, query won't return the first N for every group (group is `customer_uuid`) but it will take the first N from ALL groups. Example: I have 3 purchases from `customer1`, 3 purchases from `customer2` and 3 purchases from `customer3`. Purchases were done in the same order. So if I want first `N=2` purchases from each customer and do it as you said (the innermost `SELECT ... LIMIT 2`) it will only return the 2 purchases from `customer1` instead of 6 purchases (2 per customer) – yoyo-san Nov 03 '17 at 09:10
@RokMirt oh, I get it know. I didn't understood it quite right... maybe add the tag `greatest-n-per-group` and you need to rebuild your statement... but I don't know how. So my "Answer" is wrong. Sorry. – Cataklysim Nov 03 '17 at 09:39

score 0 · Accepted Answer · answered Nov 03 '17 at 15:20

To answer my own question: I found this. It appears to be working fine even on MySQL, not just MariaDB. Nested query needs LIMIT <large number> which creates temporary table. So Cataklysm's answer is partially correct, but it doesn't work with smaller numbers (at least not according to my testing).

Here is corrected part of the code:

...
ORDER BY pi.vehicle_uuid , p.payment_date DESC LIMIT 18446744073709551615) temp) pu

Thank you for your help.

MySQL select top n records per group nested

2 Answers2