petenaylor Posted August 28, 2012 Share Posted August 28, 2012 Hi all I have a table which is attached that has prices and a product_id. What I need to do is firstly, order by the price and then group by the product_id. I need to know how to write the mySQL query to do this. I can only group by and then sort by. Many thanks for your help. Pete Link to comment https://forums.phpfreaks.com/topic/267686-order-by-and-group/ Share on other sites More sharing options...
Barand Posted August 28, 2012 Share Posted August 28, 2012 The mysql syntax rule is "GROUP BY ... ORDER BY ...". GROUP BY is for aggregation and will result in a single row for each value of the column you group by, product_id in your case. Perhaps if gave us an example of what you are trying to achieve. Link to comment https://forums.phpfreaks.com/topic/267686-order-by-and-group/#findComment-1373128 Share on other sites More sharing options...
Barand Posted August 28, 2012 Share Posted August 28, 2012 or are are you simply needing this SELECT product_id, product, product_code, price FROM productTable ORDER BY product_id, price which will order the prices within each product_id Link to comment https://forums.phpfreaks.com/topic/267686-order-by-and-group/#findComment-1373129 Share on other sites More sharing options...
petenaylor Posted August 28, 2012 Author Share Posted August 28, 2012 Hi there Thanks for your help so far! Basically, I need to get my product IDs in order of price. So that I can query the database again to get the product information. If you look at my database, you can see that the prices can be sorted by value ascending. I need to then group the product IDs in the correct order so I can then query the database again for the full information. Many thanks Pete Link to comment https://forums.phpfreaks.com/topic/267686-order-by-and-group/#findComment-1373132 Share on other sites More sharing options...
Barand Posted August 28, 2012 Share Posted August 28, 2012 This sounds like you are going to be looping through the results of one query and performing further queries within loop. This is not a good design decision. Get the results you need first time, then process the results. Which brings me back to my original question - what exactly is the final result that you want to achieve? From the data you gave us how do expect the final output to look? Link to comment https://forums.phpfreaks.com/topic/267686-order-by-and-group/#findComment-1373133 Share on other sites More sharing options...
petenaylor Posted August 28, 2012 Author Share Posted August 28, 2012 Hi there I have restructured the database to have the 5 prices in the same table as the rest of the data. Is there anyway I can search multiple rows for the lowest value across the 5 columns and then show the results in this order? I have attached my database. Regards Pete Link to comment https://forums.phpfreaks.com/topic/267686-order-by-and-group/#findComment-1373170 Share on other sites More sharing options...
Barand Posted August 28, 2012 Share Posted August 28, 2012 That's probably the worst thing you have done. I was willing to give my time to help you but you seem too reluctant to help me by telling me exactly what you are try to arrive at. Good luck. Link to comment https://forums.phpfreaks.com/topic/267686-order-by-and-group/#findComment-1373219 Share on other sites More sharing options...
Christian F. Posted August 28, 2012 Share Posted August 28, 2012 petenaylor: I strongly recommend that you read this article! Link to comment https://forums.phpfreaks.com/topic/267686-order-by-and-group/#findComment-1373303 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.