CroiOS CroiOS - 6 months ago 8
SQL Question

How do I select an exact number of articles for each category?

Here are my tables:

CREATE TABLE IF NOT EXISTS `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(50) NOT NULL,
`post_limit` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO `category` (`id`, `title`, `post_limit`) VALUES
(1, 'News', 2),
(2, 'Sport', 2),
(3, 'Science', 1),
(4, 'Games', 1);

CREATE TABLE IF NOT EXISTS `article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(50) NOT NULL,
`category_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

INSERT INTO `article` (`id`, `title`, `category_id`) VALUES
(1, 'news article 1', 1),
(2, 'news article 2', 1),
(3, 'news article 3', 1),
(4, 'sports article 1', 2),
(5, 'sports article 2', 2),
(6, 'sports article 3', 2),
(7, 'Science article 1', 3),
(8, 'Science article 2', 3),
(9, 'games article 1', 4),
(10, 'games article 2', 4);


What I need to do is select 10 articles (
ORDER BY article.id DESC
) but bearing in mind that every category has
post_limit
, so for example we cannot take for
category_id=1
5 posts if
post_limit=2
.

Thank you in advance.

UPDATE 1:
result should be:

10 games article 2 4
8 science article 2 3
6 sports article 3 2
5 sports article 2 2
3 news article 3 1

Answer

I think you need to enumerate the articles to apply the post_limit. You can do this with a subquery:

select a.*
from (select a.*,
             (@rn := if(@c = a.category_id, @rn + 1,
                        if(@c := a.category_id, 1, 1)
                       )
             ) as rn
      from articles a cross join
           (select @rn := 0, @c := -1) params
      order by category_id
     ) a join
     category c
     on c.id = a.category_id
where a.rn <= c.post_limit
limit 10;

In practice, you probably want an order by before the limit to have more control over which articles. Similarly, you probably want another key on the order by in the subquery to control the articles . . . such as order by category_id, id desc to get the most recent articles.