Select distinct rows based on some, but not all columns

I originally ran into this problem while working on SQL queries that select certain aggregate values (min, max etc) from grouped results. For example, select the cheapest fruit, its variety and the price, off each fruit group. The common solution is to first group the fruits along with the cheapest price using MIN, then self join it to get the other column ("variety" in this case).

Now say if we have more than one variety of a fruit with the same price, and that price happened to be the lowest price. So we end up getting results like this:

Apple    Fuji         5.00
Apple    Green        5.00
Orange   valencia     3.00
Pear     bradford     6.00

How do I make it so that only one kind of apple shows up in the final result? It can be any one of the varieties, be it the record that shows up the first, last or random.

So basically I need to eliminate rows based on two of the three columns being equal, and it doesn't matter which rows get eliminated as long as there is one left in the f开发者_如何转开发inal result set.

Any help would be appreciated.

---

Try this... I added more fruits. The way to read it is to start from the inner most From clause and work your way out.

    create table fruit (
        FruitName varchar(50) not null,
        FruitVariety varchar(50) not null,
        Price decimal(10,2) not null
    )

    insert into fruit (FruitName, FruitVariety, Price)
    values ('Apple','Fuji',5.00)
    insert into fruit (FruitName, FruitVariety, Price)
    values ('Apple','Green',5.00)
    insert into fruit (FruitName, FruitVariety, Price)
    values ('Orange','Valencia',3.00)
    insert into fruit (FruitName, FruitVariety, Price)
    values ('Orange','Navel',5.00)
    insert into fruit (FruitName, FruitVariety, Price)
    values ('Pear','Bradford',6.00)
    insert into fruit (FruitName, FruitVariety, Price)
    values ('Pear','Nashi',8.00)

    select 
        rankedCheapFruits.FruitName,
        rankedCheapFruits.FruitVariety,
        rankedCheapFruits.Price
    from (
        select 
            f.FruitName,
            f.FruitVariety,
            f.Price,
            row_number() over(
                partition by f.FruitName
                order by f.FruitName, f.FruitVariety
                ) as FruitRank
        from (
            select 
                f.FruitName, 
                min(f.Price) as LowestPrice
            from   Fruit f
            group by 
                f.FruitName
            ) as cheapFruits
            join Fruit f on cheapFruits.FruitName = f.FruitName
                and f.Price = cheapFruits.LowestPrice
        ) rankedCheapFruits
    where rankedCheapFruits.FruitRank = 1

---

You could use a MIN operator, that would limit it to the first row

---

One option is to rank the rows based on some criteria (alphabetical order of fruit variety) and then pick the minimum of the rank.

There is a rank() function in ms-sql for exactly this purpose.