...

View Full Version : ASP Group By



nueu
09-22-2007, 06:19 PM
I have the following statement that is working just fine:

SQL = "select* from products GROUP BY name ORDER BY price"

I have many products with the same name but with different prices and I want to display unique records along with other information but I want to display the cheapest product first. It is displaying the first record in the table with that name but not the cheapest distinct record in the list of all distinct product names.
Not sure of the above makes sense so this is the kind of data in the table.

Name= Test1, Price= 25
Name= Test2, Price= 20
Name= Test1, Price= 15
Name= Test3, Price=30

When listing the products it will show the following order:

Test2, 20
Test1, 25
Test3, 30

However I want it to display:

Test1, 15
Test2, 25
Test3, 30

Thanks for any help.

Daniel

SouthwaterDave
09-23-2007, 11:55 AM
I don't know which database server you are using, but this is how to do it in SQL Server:

SQL = "select name, min(price) from products group by name order by name"

I expect this would work in MySQL too, but I am not familiar with MySQL, so I cannot be sure.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum