Home > Archive > PHP Language > August 2005 > One query and problem LIMIT
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
One query and problem LIMIT
|
|
|
| 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.
| |
| Stefan Rybacki 2005-08-07, 8:59 am |
| 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
| |
|
| Thanks Stefan,
It works when modified (the 2nd. JOIN):
SELECT t1.categoryid, t2.articleid
FROM category t1
LEFT JOIN article t2 ON t1.categoryid = t2.categoryid
LEFT JOIN article t2_2 ON t1.categoryid = t2_2.categoryid
WHERE t2.articleid <= t2_2.articleid OR t2.categoryid IS NULL
GROUP BY t1.categoryid, t2.articleid
HAVING count(*) <= 5;
THX
"Stefan Rybacki" <stefan.rybacki@gmx.net> a écrit dans le message de news:
3lmbm6F13f09lU1@individual.net...
> rbaba wrote:
>
> 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
|
|
|
|
|