1

I have news table from where I need to select specific number or rows - for example 3 news from 4 categories. There should always be 3 news from each category (table is full of contents - it's not the matter). I want to select only the newest ones.

Suppose my table contains 5 fields: id_news, news_title, news_desc, news_date, id_news_category.

Edit: Looks like those previous questions can't help me because what I really need to do is to select top N from range of categories id, for eg. 1-6, 7-13, 14-20 etc.

I'm looking for the best (most efficient) way to accomplish this. Is it UNION operator:

    SELECT id_news, news_title 
    FROM news
    WHERE id_news_category BETWEEN 1 AND 6
    ORDER BY news_date DESC 
    LIMIT 3
    UNION
    SELECT id_news, news_title 
    FROM news
    WHERE id_news_category BETWEEN 7 AND 13
    ORDER BY news_date DESC 
    LIMIT 3
    UNION
    SELECT id_news, news_title 
    FROM news
    WHERE id_news_category BETWEEN 14 AND 20
    ORDER BY news_date DESC 
    LIMIT 3
    UNION
    SELECT id_news, news_title 
    FROM news
    WHERE id_news_category BETWEEN 21 AND 27
    ORDER BY news_date DESC 
    LIMIT 3

I know above UNION may have quite other form - with single order. Not really sure if it has performance impact. I'm using PSql 8.4 and Symfony so I will need to use QueryBuilder or maybe just native query.

webrama.pl
  • 1,870
  • 1
  • 23
  • 36
  • Do you need *random* news? Or always the same news? Or there is some other criteria (a part from the category)? – Renzo Sep 20 '15 at 07:27
  • Good point. There have to be always the newest ones. Edited. – webrama.pl Sep 20 '15 at 07:33
  • 1
    At least using `UNION ALL` will be better if the items cannot be duplicates or you can allow duplicates from different categories. – Sami Kuhmonen Sep 20 '15 at 07:35
  • Sure. It's faster. There can't be duplicates. – webrama.pl Sep 20 '15 at 07:35
  • 1
    AKA [tag:greatest-n-per-group]. [You want to modify the top answer](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) to read `rk < 4`. – Clockwork-Muse Sep 20 '15 at 07:36
  • I edited my question and i'm still looking for some advices. – webrama.pl Sep 20 '15 at 21:19
  • Also you can vote to reopen your own question. Click to "reopen". And explain more clearly, why it isn't duplicate, it improves the (unfortunately, still low :-( ) chances. – peterh Sep 21 '15 at 00:04

0 Answers0