How to SELECT the newest four items per category?

user_107459 0
Viewed 17419

I have a database of items. Each item is categorized with a category ID from a category table. I am trying to create a page that lists every category, and underneath each category I want to show the 4 newest items in that category.

For Example:

Pet Supplies

img1
img2
img3
img4

Pet Food

img1
img2
img3
img4

I know that I could easily solve this problem by querying the database for each category like so:

SELECT id FROM category

Then iterating over that data and querying the database for each category to grab the newest items:

SELECT image FROM item where category_id = :category_id 
ORDER BY date_listed DESC LIMIT 4

What I'm trying to figure out is if I can just use 1 query and grab all of that data. I have 33 categories so I thought perhaps it would help reduce the number of calls to the database.

Anyone know if this is possible? Or if 33 calls isn't that big a deal and I should just do it the easy way.

greatest-n-per-group mysql sql
8 Answers

Recently I came across a similar situation, I tried a query that worked for me which is independent on database 

SELECT i.* FROM Item AS i JOIN Category c ON i.category_id=c.id WHERE
(SELECT count(*) FROM Item i1 WHERE 
i1.category_id=i.category_id AND 
i1.date_listed>=i.date_listed) <=3 
ORDER BY category_id,date_listed DESC;

It is equivalent to running 2 for loops and checking if items newer than this are less than 3

user_51389230
Related