• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

MySQL - need to limit results to 1 from each category?

Hi,
how can I limit the below query to only select one item from each category id?

Thanks

Andrew


SELECT DISTINCT items.id AS id, items.itemName, items.link_id, items.itemDesc, items.itemDesc2, items.itemDesc3, items.itemPrice, items.image1, items.image2, items.image3, items.image4, items.image5, items.image6, items.image7, items.item_type, items.min_order, items.prodcode, items.true_stock_count, items.display_stock_count, items.low_stock_count, items.limit_availability, items.date_time_expires, items.itemDescb, items.multi_price, items.itemPrice, items.retPrice, items.specialPrice, items.specs1, items.spechead1, items.specs2, items.spechead2, items.add_comments, items.more_details, category, items_to_category.position, category.subcat, category.position as cat_order, category.cat_type FROM items_to_category LEFT JOIN category ON category.id = items_to_category.category LEFT JOIN items ON items.id = items_to_category.item WHERE category.id IN ('157','159','160','158') GROUP BY id ORDER BY cat_order, category, position

Open in new window

0
sabecs
Asked:
sabecs
1 Solution
 
Walter RitzelSenior Software EngineerCommented:
I think this could be a solution. I took the assumption  that you want the item with the max id for each category. If you have a specific requirement on which item to select from each category, then you can change the query:
SELECT items.id AS id
, items.itemName
, items.link_id
, items.itemDesc
, items.itemDesc2
, items.itemDesc3
, items.itemPrice
, items.image1
, items.image2
, items.image3
, items.image4
, items.image5
, items.image6
, items.image7
, items.item_type
, items.min_order
, items.prodcode
, items.true_stock_count
, items.display_stock_count
, items.low_stock_count
, items.limit_availability
, items.date_time_expires
, items.itemDescb
, items.multi_price
, items.itemPrice
, items.retPrice
, items.specialPrice
, items.specs1
, items.spechead1
, items.specs2
, items.spechead2
, items.add_comments
, items.more_details
, category
, items_to_category.position
, category.subcat
, category.position as cat_order
, category.cat_type 
FROM items_to_category LEFT JOIN category ON category.id = items_to_category.category 
                       LEFT JOIN items ON items.id = items_to_category.item 
WHERE items.id in (select max_id from (select category.id, max(items.id) from  items_to_category LEFT JOIN category ON category.id = items_to_category.category 
                       LEFT JOIN items ON items.id = items_to_category.item  where category.id IN ('157','159','160','158') group by category.id))
ORDER BY cat_order, category, position

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now