View Full Version : ASP Group By

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.


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.