How to get first top 10 records of campaing_ids?

Question

I want each campaign's first 10 records and trying below but it is giving me campaing_id = 1's first 20 records.

How can I get records of each caimpaingn's first 10 records. Below query I have tried.

SELECT CG.*, COUNT(*) AS ageCount, DATEDIFF( NOW(), CG.domain_creation_date ) AS age, CASE
                    WHEN DATEDIFF( NOW(), CG.domain_creation_date ) >= 365 THEN 'old' 
                    WHEN (DATEDIFF(NOW(), CG.domain_creation_date) < 365 AND DATEDIFF(NOW(), CG.domain_creation_date) > 180) THEN 'young'
                    WHEN DATEDIFF(NOW(), CG.domain_creation_date) <= 180 THEN 'new'
                    END AS ageType,
                    CG.date_added
                    FROM serp_google_crawl_data AS CG
                    WHERE CG.date_added = '2014-05-26'  AND CG.campaign_id IN (1,2)  GROUP BY CG.rank ORDER BY CG.rank, CG.id LIMIT 0, 20

I am using mysql. Thanks.

I'll use Union, 3 select statemens, each one diferent category where clasue filtering limitin 10 rows each — Horaciux, May 26 '14 at 16:23
I added the [tag:greatest-n-per-group] tag and marked this question as a duplicate of one specific question that is very similar. If that doesn't fit your needs, check out other questions with the same tag. Most of such questions are greatest-**1**-per-group, which is done differently. But about 1 in 10 are greatest-n-per-group. The point is, this type of query has been answered many times on StackOverflow. — Bill Karwin, May 26 '14 at 17:35

score 0 · Answer 1 · answered May 26 '14 at 17:26

Here is what I mean:

SELECT CG.*, COUNT(*) AS ageCount, DATEDIFF( NOW(), CG.domain_creation_date ) AS age,
    'new' ageType,  CG.date_added
    FROM serp_google_crawl_data AS CG
    WHERE CG.date_added = '2014-05-26'  AND CG.campaign_id IN (1,2)  
    AND DATEDIFF(NOW(), CG.domain_creation_date) <= 180
    GROUP BY CG.rank ORDER BY CG.rank, CG.id LIMIT 0, 10

UNION

SELECT CG.*, COUNT(*) AS ageCount, DATEDIFF( NOW(), CG.domain_creation_date ) AS age,
    'young' ageType,    CG.date_added
    FROM serp_google_crawl_data AS CG
    WHERE CG.date_added = '2014-05-26'  AND CG.campaign_id IN (1,2)  
    AND (DATEDIFF(NOW(), CG.domain_creation_date) BETWEEN 364 AND 179) 
    GROUP BY CG.rank ORDER BY CG.rank, CG.id LIMIT 0, 10                    

UNION

SELECT CG.*, COUNT(*) AS ageCount, DATEDIFF( NOW(), CG.domain_creation_date ) AS age,
    'old' ageType,  CG.date_added
    FROM serp_google_crawl_data AS CG
    WHERE CG.date_added = '2014-05-26'  AND CG.campaign_id IN (1,2)  
    AND  DATEDIFF( NOW(), CG.domain_creation_date ) >= 365
    GROUP BY CG.rank ORDER BY CG.rank, CG.id LIMIT 0, 10

How to get first top 10 records of campaing_ids?

1 Answers1