开发者

Select articles from distinct categories

开发者 https://www.devze.com 2023-03-27 04:51 出处:网络
I have a table with some articles named \"articles\" with the following columns&rows id - category -title - content - date

I have a table with some articles named "articles" with the following columns&rows

id - category -  title - content - date
1 - 1 - my title - my content - time here
2 - 2 - my title - my content - time here
3 - 1开发者_开发百科 - my title - my content - time here
4 - 1 - my title - my content - time here
5 - 3 - my title - my content - time here
6 - 1 - my title - my content - time here
7 - 2 - my title - my content - time here
8 - 3 - my title - my content - time her
9 - 4 - my title - my content - time here
10 - 4 - my title - my content - time here

I need to select 2 distinct articles only from categories 1,2 and 3

My code so far is:

SELECT * FROM articles WHERE category IN ('1', '2', '3') ORDER BY date DESC LIMIT 6


How about something like

SELECt  *
FROm    articles a
WHERE   category IN ('1', '2', '3')
AND     ID IN   (
                    SELECT  ID
                    FROM    articles
                    WHERE   category = a.category
                    ORDER BY date DESC 
                    LIMIT 2
                )


Just replace you limit from 6 to 2 your code works fine :

SELECT * FROM articles WHERE category IN ('1', '2', '3') ORDER BY date DESC LIMIT 2;
0

精彩评论

暂无评论...
验证码 换一张
取 消