0

I have 2 tables: x_products and x_category

I have to select the last 3 products inserted (order by x_products.data column, descending) for each category using a single query.

My idea was to use an INNER JOIN technique (for using a single query).

I've tried to use LIMIT 0,3, but only the last 3 products are returned (3 per total, but not for each category.

SELECT products.* 
FROM x_products products
INNER JOIN x_category categories
ON products.category = categories.id
ORDER BY products.data DESC LIMIT 0,3

How could I select 3 products for each category, but not 3 products per total?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
RazvanZ
  • 218
  • 2
  • 10
  • Look at the `GROUP BY` statement – Wolph Jan 22 '15 at 12:55
  • Check this http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results\ – Abhik Chakraborty Jan 22 '15 at 13:02
  • Still struggling? If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Jan 22 '15 at 13:02

1 Answers1

-1

Something like this might help,

SELECT products.* 
FROM x_products products
INNER JOIN x_category categories
ON products.category = categories.id
GROUP BY products.category
ORDER BY products.data DESC LIMIT 0,3

And yeah you can find a few examples here

bSaraogi
  • 148
  • 9