PHP/MySQL - Select One of Each Type In Table

Question

I'm creating a blog with many different categories and on my home page I'd like to show one post from each category (except maybe "miscelanious") but I'm not sure if there's a simple one line solution for this. I'm hoping for something along the lines of:

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

Is something like this possible?

Jay Blanchard · Answer 1 · 2015-04-21T16:00:19.650

5

Just GROUP BY the category -

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

edited Apr 21 '15 at 16:00

answered Apr 21 '15 at 15:45

Jay Blanchard

34,243
16
77
119

This would still fetch all entries and not only one per entry (although it display only one per entry) and hence could take longer on a large table than manually getting one via LIMIT? Or is my thinking wrong? – kero Apr 21 '15 at 15:47
It does fetch them all, but will show one per category. The one it shows could be random though. Since we really don't know what the OP wants as output it would be hard to say beyond this. – Jay Blanchard Apr 21 '15 at 15:48
Thanks @JayBlanchard, it does show only one of each category type. I would ideally like it to show the latest one, ie. `order by 'added' desc` – Foot Promoter Apr 21 '15 at 15:53
If `added` is a date you can select the `MAX()` added date and then perform the group by. It's hard to know for sure without seeing your table config. – Jay Blanchard Apr 21 '15 at 15:57
Thanks @JayBlanchard, I tried to add MAX() but it gave me an error (`Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in`) so I've reverted back to using it without! – Foot Promoter Apr 21 '15 at 15:59
OK - I'll remove from answer. – Jay Blanchard Apr 21 '15 at 16:00
@JayBlanchard, Thanks - However I would like it to order by the date as currently it's showing everything older than newer :( – Foot Promoter Apr 21 '15 at 16:03

A l w a y s S u n n y · Answer 2 · 2015-04-21T15:52:32.753

1

You can try this way GROUP BY your category. see more about 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

edited Apr 21 '15 at 15:52

answered Apr 21 '15 at 15:46

A l w a y s S u n n y

36,497
8
60
103

score 1 · Answer 3 · edited May 23 '17 at 11:45

If you want to find the newest entry in each category, you're going to have to figure out which entry is newest, then join to back to it -- you can do this with a subselect, 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 -- best to join on a PK if possible)

This question gives a pretty detailed response to the general problem.

PHP/MySQL - Select One of Each Type In Table

3 Answers3

Linked