Order by and group

[petenaylor]

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

[Barand]

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.

[Barand]

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

[petenaylor]

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

[Barand]

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?

[petenaylor]

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

[Barand]

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.

[Christian F.]

petenaylor: I strongly recommend that you read this article!