php-sql limit query per category

Question

I wanted to limit my query per category, I've seen a lot of same topic here but too complicated so I will ask another.

for example I have

id       title        category
1        one          number
2        two          number
3        three        number  
4        four         number
5        a            letter
6        b            letter
7        c            letter

and I wanted to limit my query, let say 2 per category so I have on my output like these

one
two

a
b

possible duplicate of http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group — pankar, Jul 25 '12 at 10:44

score 1 · Answer 1 · answered Jul 25 '12 at 12:39

I got answer from diff topic I'll post it here for others who will drop in this same question

SELECT * FROM (
    SELECT
       table.*,
       @rn := CASE WHEN @category=category THEN @rn + 1 ELSE 1 END AS rn,
       @category := category
    FROM table, (SELECT @rn := 0, @category := NULL) AS vars        
    ORDER BY category
) AS T1
WHERE rn <= 2

score 0 · Answer 2 · answered Jul 25 '12 at 11:07

0

Created using this link, there's an explanation there. This also works for a large number of categories, but beware, it can become very slow without the right keys defined.

set @num := 0, @category := '';

select title, category, @num := if(@category = category, @num +1, 1) as row_number,
@category := category as dummy

from test
group by category, title
having row_number <=2;

answered Jul 25 '12 at 11:07

RobertDiep

251
1
6

I'm newbie here ... where do I need to put "set num := 0, category := ''; " I got error over this line – Ivo San Jul 25 '12 at 11:28
Are you trying to do this through PHP? With PHPMyAdmin you can just put it in the SQL box. – RobertDiep Jul 25 '12 at 12:33

score -3 · Answer 3 · answered Jul 25 '12 at 10:44

Tell me if i'm getting you right -
That's the sql -

SELECT * FROM `categories` ORDER BY `id` ASC LIMIT 0,2

What I did is this: Select all the items in categories table and order it by the id row, limit it for two results only and order it from the beggining

php-sql limit query per category

3 Answers3