CODEWITHSUNDEEP
phpmysqlselect
Foot Promoter
Foot Promoter

Reputation: 65

PHP/MySQL - Select One of Each Type In Table

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?

Upvotes: 3

Views: 2289

Answers (3)

Jay Blanchard
Jay Blanchard

Reputation: 34416

Just GROUP BY the category - 

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

Upvotes: 5

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38502

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

Upvotes: 1

Carson Moore
Carson Moore

Reputation: 1287

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.

Upvotes: 1

Related Questions