how to get multiple records from one table using group by query in mysql

Question

ContentDetails Table has below contentTypes. contentType -- live , vod , movie.

Each contentType has 100 reocrds (live -100,vod-100,movie - 100) .

I want multiple records from each contentType.

Query :

 SELECT * FROM (SELECT * FROM  `ContentDetails ` ORDER BY `contentid   ` ASC ) AS a GROUP BY `contentType` limit 0,100;

After exceuting aove query getting below results .

only one live , one vod and one movie comes.

But my requirement is:

10 live contents , 10 vod contents and 10 movie contens.

score 1 · Accepted Answer · answered Mar 25 '16 at 06:33

1

May be you want 10 items from each content type, if yes then you may use below query-

SELECT x.*
FROM (SELECT t.*,
               CASE 
                 WHEN @category != t.contentType THEN @rownum := 1 
                 ELSE @rownum := @rownum + 1 
               END AS rank,
               @category := t.contentType AS var_category
          FROM contentDetails t
          JOIN (SELECT @rownum := NULL, @category := '') r     
      ORDER BY t.contentType,t.contentid) X
      WHERE x.rank<=10;

answered Mar 25 '16 at 06:33

Zafar Malik

6,734
2
19
30

Tank you Its working fine . But i do not understand above query. Could you explain this query. – Pyla Srenu Mar 25 '16 at 06:44
If it helps..you may choose this as accepted answer by green stick. :) – Zafar Malik Mar 25 '16 at 07:11

how to get multiple records from one table using group by query in mysql

1 Answers1