Group By and Aggregate function

Question

I want to write an efficient query which returns a list of fruits by type, the lowest price for the type of fruit and the name of the fruit. Right now, I have a query which return me the fruit type and the lowest price for that type (see below). But I am unable to get the name of the cheapest fruit.

Any idea how I can achieve that? Thanks.

CREATE TABLE Fruits (
    [type] nvarchar(250),
    [variety] nvarchar(250),
    [price] money
)
GO

INSERT INTO Fruits VALUES ('Apple', 'Gala', 2.79)
INSERT INTO Fruits VALUES ('Apple', 'Fuji', 0.24)
INSERT INTO Fruits VALUES ('Apple', 'Limbertwig', 2.87)
INSERT INTO Fruits VALUES ('Orange', 'Valencia', 3.59)
INSERT INTO Fruits VALUES ('Pear', 'Bradford', 6.05)

SELECT type, MIN(price)
FROM   Fruits
GROUP BY [type]

Answer 1

Use:

SELECT f.*
  FROM FRUITS f
  JOIN (SELECT t.type,
               MIN(t.price) AS min_price
          FROM FRUITS t
      GROUP BY t.type) x ON x.type = f.type
                        AND x.min_price = f.price

I gather you're using SQL Server - if v2005 or newer, you could also use analytic/rank/windowing functions instead:

SELECT f.type, f.variety, f.price
  FROM (SELECT t.type, t.variety, t.price,
               ROW_NUMBER() OVER (PARTITION BY t.type ORDER BY t.price) AS rank
          FROM FRUITS t) f
 WHERE f.rank = 1

Answer 2

There are a number of ways to do this, one solution is below.

SELECT F2.type, f2.variety, f2.price
FROM 
(
    SELECT type, min(price) as price
    FROM Fruits
    GROUP BY [type]
) as MinData
    INNER JOIN Fruits F2
        ON (MinData.type = Type = F2.Type
            AND MinData.price = F2.Price)

Keep in mind that if you have multiple items in a category with the same price at the minimum you will get multiple results.

Answer 3

There's a simple trick you can use for this sort of query if your table has a surrogate primary key. (Actually, you can do it without one, but it's more convoluted.)

The setup:

if object_id('tempdb..#Fruits') is not null drop table #Fruits
create table #Fruits (
  [id] int identity(1,1) not null,
  [type] nvarchar(250),
  [variety] nvarchar(250),
  [price] money
)

insert into #Fruits ([type], [variety], [price])
select 'Apple', 'Gala', 2.79 union all
select 'Apple', 'Fuji', 0.24 union all
select 'Apple', 'Limbertwig', 2.87 union all
select 'Orange', 'Valencia', 3.59 union all
select 'Pear', 'Bradford', 6.05

And now the SQL:

select * -- no stars in PROD!
from #Fruits a
where
   a.id in (
      select top 1 x.id
      from #Fruits x
      where x.[type] = a.[type]
      order by x.price
   )