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 Quote Link to comment 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? Quote Link to comment 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. Quote Link to comment 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. Quote Link to comment 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? Quote Link to comment Share on other sites More sharing options...
fulleffect Posted June 11, 2008 Author Share Posted June 11, 2008 Anyone have any code suggestions please? Quote Link to comment Share on other sites More sharing options...
fulleffect Posted June 12, 2008 Author Share Posted June 12, 2008 Can anyone help please? Quote Link to comment 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.