ManiMuthuPandi ManiMuthuPandi - 4 months ago 10
MySQL Question

Select 2 rows in every category

I am having below Query.

This Query is to fetch 2 rows in every industry_id. So if there are 10 unique industry_id means, Query should return 2 rows from each industry_id.

SET @a := 0;

SELECT
*
FROM
(
SELECT
@a :=@a + 1 AS row_count,
p.*, u.first_name,
u.designation,
u.profile_picture,
i. NAME AS industry,
l.location
FROM
industry i,
projects p,
location l,
user_profile u
WHERE
u.uid = p.uid
AND p.location_id = l.id
AND i.id = p.industry_id
AND p.abstract != ''
) t
WHERE row_count <= 2


But this query always return 2 rows from only one industry_id.

How to achieve to get 2 rows from each industry_id

Answer

Please give it a try:

SELECT
    *
FROM
    (
        SELECT
            IF(i.id = @sameIndustryId, @a :=@a + 1, @a := 1) AS row_count,
            @sameIndustryId := i.id,
            p.*, u.first_name,
            u.designation,
            u.profile_picture,
            i. NAME AS industry,
            l.location
        FROM
            industry i,
            projects p,
            location l,
            user_profile u,
           (SELECT @a := 1, @sameIndustryId := 0 ) var
        WHERE
            u.uid = p.uid
        AND p.location_id = l.id
        AND i.id = p.industry_id
        AND p.abstract != ''
        ORDER BY i.id
    ) t
WHERE   row_count <= 2

Note: Try to avoid IMPLICIT joins. It gets unclear when you are trying to establish relationship among 3 or more tables. Better use INNER JOIN. Because it shows better the relationship.

SELECT
    *
FROM
    (
        SELECT
            IF(i.id = @sameIndustryId, @a :=@a + 1, @a := 1) AS row_count,
            @sameIndustryId := i.id,
            p.*, u.first_name,
            u.designation,
            u.profile_picture,
            i. NAME AS industry,
            l.location
        FROM industry i
                INNER JOIN projects p ON i.id = p.industry_id
        INNER JOIN location l ON p.location_id = l.id
        INNER JOIN user_profile u ON u.uid = p.uid
        CROSS JOIN (SELECT @a := 1, @sameIndustryId := 0 ) var
        WHERE p.abstract != ''
        ORDER BY i.id
    ) t
WHERE   row_count <= 2;