ORDER BY Question

use order by but group results?

[hughie]

Hi ALL

Here's a basic database called "products"

ID - Product_Name - Product_ID - Price_Sold
1 - Fishing Rod - 10 - £8.00
2 - Net - 32 - £5.00
3 - Fishing Rod - 10 - £15.00
4 - Boots - 23 - £11.00
5 - Bait - 89 - £9.00

What's happening at the moment is that i order the results by sale price;

SELECT Product_Name, Price_Sold FROM products ORDER BY Price_Sold DESC

and return;
Fishing Rod - £15.00
Boots - £11.00
Bait - £9.00
Fishing Rod - £8.00
Net - £5.00

What i would like to to is create a similar query but where two ID's are the same, have them group together and return with the duplicate product in order, something like:

Fishing Rod - £15.00
Fishing Rod - £8.00
Boots - £11.00
Bait - £9.00
Net - £5.00

Is that possible in one SQL statement or do i have to run several?

[henry0]

It could be easy if you add another col: category
for ex fr for fishing rod etc..
each cate could be called by a variable here $cat
then:
SELECT Product_Name, Price_Sold, category FROM products where category ='$cat' ORDER BY Price_Sold DESC

<EDIT>
Forgot something; I think you want also to show many items and category in one shot
so you need to use GROUP
review that easy EXAMPLE [dev.mysql.com]
</EDIT>

[hughie]

yeah but i wouldn't then get a list ordered by selling price, i'd just get them ordered by category first, then selling price

I want them ordered by selling price, then if there's a matching Prod_ID, inturupt the list and spit out the remaining matching Prod_ID's regardless of selling price, then carry on as before.

the logic would go something like

-> Get all from products
-> list top price
? are there any matching products
NO
-> list next top price
? are there any matching products
YES
---> list remaining matching products
->List next top price etc...

[mcibor]

Hi hughie!

What I would do is first order by name and then by price:

SELECT Product_Name, Price_Sold FROM products ORDER BY Product_Name, Price_Sold DESC

It's not a perfect solution, but may work for you:

Bait - £9.00
Boots - £11.00
Fishing Rod - £15.00
Fishing Rod - £8.00
Net - £5.00

Appart from that you would require either nested queries, or sort with PHP

PS. Sorting with PHP would be faster than nested queries. However, the latter is much simpler to write:

SELECT Product_Name FROM products GROUP BY Product_Name ORDER BY Price_Sold DESC
while $results = mysql_fetch_assoc...
{
SELECT Price_Sold FROM products WHERE Product_Name = $result['Product_Name']

}

And you've got what you wished for
Regards
Michal

[hughie]

Hi Michal

Thanks for the reply, as i thought, there isn't a cunning sql query to call upon.

I shall implement your version now, many thanks!

Hughie