Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: ASP Group By
09-22-2007, 06:19 PM #1
- Join Date
- Sep 2007
- Thanked 0 Times in 0 Posts
ASP Group By
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:
However I want it to display:
Thanks for any help.
09-23-2007, 11:55 AM #2
- Join Date
- May 2007
- Thanked 18 Times in 18 Posts
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.