Clever Geek Handbook

PHP / MySQL - select one of each type in the table

I am creating a blog with many different categories, and on my home page I would like to show one post from each category (except maybe the "buggy" one), but I'm not sure if there is a simple one-line solution for this. I am hoping for something like:

"SELECT * FROM blogs WHERE cat != 'misc' ORDER BY added LIMIT (ONE OF EACH CAT TYPE)"

Is this possible?

+3


source to share


3 answers


Just a GROUP BY

category -



SELECT * 
FROM blogs WHERE cat != 'misc' 
GROUP BY cat
ORDER BY added
+5


source


You can try GROUP BY

your own category. more on GROUP BY

https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html



SELECT * FROM blogs WHERE cat != 'misc' GROUP BY cat ORDER BY added
+1


source


If you want to find the newest post in each category, you will need to figure out which post is the newest and then join it - you can do this with a subquery if necessary:

SELECT b.* 
FROM blogs `b`
INNER JOIN (SELECT `category`, MAX(`added`) AS `max_add` FROM `blogs` GROUP BY `category` WHERE `category` != 'misc') `a`
ON `a`.`category` = `b`.`category` AND `a`.`max_add` = `b`.`added`

(or something similar - it's best to join a PC if possible)

This question provides a fairly detailed answer to a common problem.

+1


source






More articles:


All Articles