fulleffect Posted June 11, 2008 Share Posted June 11, 2008 Hello Everyone, I have 10 Categories and 100 Products, which is 10 products per category. I would like to find the top 1 best selling product from each category based on number of orders. Categories T-Shirts - Top 1 Trousers - Top 1 Hats - Top 1 Gloves - Top 1 Shirts - Top 1 Skirts - Top 1 Underwear - Top 1 Socks - Top 1 Jumpers - Top 1 Shoes - Top 1 instead of........... T-Shirts Trousers - Top 4 Hats Gloves Shirts - Top 2 Skirts Underwear - Top 6 Socks Jumpers Shoes This is the existing SQL, but it takes the top 10 from across all categories, which results in the top 10 being taken from only 3 categories instead of top 1 from all categories. select distinct p.products_id, p.products_price, p.products_image, pd.products_name from products p, products_description pd, products_to_categories p2c, categories c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id order by p.products_ordered desc, pd.products_name limit 10 Link to comment https://forums.phpfreaks.com/topic/109713-select-top-1-product-from-10-categories/ Share on other sites More sharing options...
luca200 Posted June 11, 2008 Share Posted June 11, 2008 Do you have any products being part of more than 1 category? Link to comment https://forums.phpfreaks.com/topic/109713-select-top-1-product-from-10-categories/#findComment-562990 Share on other sites More sharing options...
fulleffect Posted June 11, 2008 Author Share Posted June 11, 2008 No Luca200, But the software has the capability to do that, thats why distinct is being used i guess. Link to comment https://forums.phpfreaks.com/topic/109713-select-top-1-product-from-10-categories/#findComment-563009 Share on other sites More sharing options...
fenway Posted June 11, 2008 Share Posted June 11, 2008 You need to get all categories first, then find the right one for each. Link to comment https://forums.phpfreaks.com/topic/109713-select-top-1-product-from-10-categories/#findComment-563051 Share on other sites More sharing options...
fulleffect Posted June 11, 2008 Author Share Posted June 11, 2008 Do you have a code example please? Link to comment https://forums.phpfreaks.com/topic/109713-select-top-1-product-from-10-categories/#findComment-563133 Share on other sites More sharing options...
fulleffect Posted June 11, 2008 Author Share Posted June 11, 2008 Anyone have any code suggestions please? Link to comment https://forums.phpfreaks.com/topic/109713-select-top-1-product-from-10-categories/#findComment-563510 Share on other sites More sharing options...
fulleffect Posted June 12, 2008 Author Share Posted June 12, 2008 Can anyone help please? Link to comment https://forums.phpfreaks.com/topic/109713-select-top-1-product-from-10-categories/#findComment-563723 Share on other sites More sharing options...
fenway Posted June 12, 2008 Share Posted June 12, 2008 Do you have a code example please? No need to bump your post 3 times in 24hrs. Write a query to get all of the categories (e.g. with a group by), and then join this to the ordered products... it's that simple Link to comment https://forums.phpfreaks.com/topic/109713-select-top-1-product-from-10-categories/#findComment-564096 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.