Posted by Stefan Rybacki on 11/10/65 11:23

rbaba wrote:
> Hi all,
>
> I have 2 tables: articles and categories
>
> articles table:
> ------------
> articleid
> categoryid
> name
>
> categories table:
> ---------------
> categotyid
> name
> description
>
>
> I want to get the last entred 5 articles for each category with one quey:
>
> like this:
>
> categoryid | articleid
> 1 | 29
> 1 | 12
> 1 | 11
> 1 | 10
> 1 | 2
>
> 2 | 25
> 2 | 20
> 2 | 16
> .
> .
> .
> N | 15
> N | 9
> N | 7
> N | 6
> N | 5
>
> As you see:
> For categoryid=1 I get 5 articles because there are more than 5 articles for
> this category in the DB
> For categoryid=2 I get only 3 articles because there are only 3 articles for
> this category in the DB
>
> How can I do this ?
>
> THX.
>
>

Try something like this:

SELECT t1.name, t2.name
FROM category t1
LEFT JOIN article t2 ON t1.categoryid = t2.categoryid
LEFT JOIN article t2_2 ON t1.categoryid = t2.categoryid
WHERE t2.name <= t2_2.name OR t2.categoryid IS NULL
GROUP BY t1.categoryid, t1.name, t2.name
HAVING count(*) <= 5;

Regards
Stefan

• Next in forum: Key()
• Prev in forum: Re: How to access second element of the array $_REQUEST?
• Thread view: Re: One query and problem LIMIT

[Reply to this message]