Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2

Thread: ASP Group By

  1. #1
    New to the CF scene
    Join Date
    Sep 2007
    Posts
    1
    Thanks
    0
    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:

    Test2, 20
    Test1, 25
    Test3, 30

    However I want it to display:

    Test1, 15
    Test2, 25
    Test3, 30

    Thanks for any help.

    Daniel

  • #2
    Regular Coder
    Join Date
    May 2007
    Location
    UK
    Posts
    180
    Thanks
    0
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •