How to select the first/least/max row per group in SQL - 快车库

表：

CREATE TABLE `fruits` (
  `type` varchar(10) DEFAULT NULL,
  `variety` varchar(10) DEFAULT NULL,
  `price` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

数据：

INSERT INTO `fruits` (`type`, `variety`, `price`)
VALUES
    ('apple', 'gala', 2.79),
    ('apple', 'fuji', 0.24),
    ('apple', 'limbertwig', 2.87),
    ('orange', 'valencia', 3.59),
    ('orange', 'navel', 9.36),
    ('pear', 'bradford', 6.05),
    ('pear', 'bartlett', 2.14),
    ('cherry', 'bing', 2.55),
    ('cherry', 'chelan', 6.33);

查询

select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type)
   or price = (select min(price) from fruits as f where f.type = fruits.type
      and price > (select min(price) from fruits as f2 where f2.type = fruits.type));

结果：

apple    gala    2.79
apple    fuji    0.24
orange    valencia    3.59
orange    navel    9.36
pear    bradford    6.05
pear    bartlett    2.14
cherry    bing    2.55
cherry    chelan    6.33

原文