Group by and Order By Problem

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

What version of SS are you working with?

In case of having same product with same price multiple times, then we need a tie breaker in order to make the solution deterministic.

select *

from dbo.product as a

where not exists (

select *

from dbo.product as b

where b.[name] = a.[name] and b.price < a.price

)

select *

from dbo.product as a

where price = (

select min(b.price)

from product as b

where b.[name] = a.[name]

)

select *

from dbo.product as a

where price = (

select top 1 b.price

from product as b

where b.[name] = a.[name]

order by b.price

)

-- 2005

with cte

as

(

select *, row_number() over(partition by [name] order by price) rn

from dbo.product

)

select *

from cte

where rn = 1

go

AMB

|||Just a kind reminder, the 2000 and 2005 solution may not return the same results.
Such as
Name= Test1, Price= 25
Name= Test2, Price= 20
Name= Test1, Price= 15
Name= Test3, Price=30
Name= Test1, Price=15

2000 Version return:

Test1,15
Test2,20
Test3,30
Test1,15

2005 version return:
Test1,15
Test2,20
Test3,30

To make two solutions equal, it needs add DISTINCT to outer select.

Such as:

select DISTINCT *

from dbo.product as a

where not exists (

select *

from dbo.product as b

where b.[name] = a.[name] and b.price < a.price

)

|||

You are right. I should have used dense_rank instead row_number or "distinct" as you mentioned. I would prefer to include a second column, like transaction_id, as the tie breaker.

AMB