Most / least expensive dealer

mind_blowing_sql.sql

-- in mysql, create "shop" table with article, dealer, and price
CREATE TABLE shop (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, article INT, dealer VARCHAR(100), price DOUBLE);

-- populate some records
-- article 1
INSERT INTO shop (article, dealer, price) VALUES (1, "jeff", 100.0);
INSERT INTO shop (article, dealer, price) VALUES (1, "bill", 101.0);
-- article 2
INSERT INTO shop (article, dealer, price) VALUES (2, "jeff", 30);
INSERT INTO shop (article, dealer, price) VALUES (2, "bill", 30);

-- find most expensive dealer via mind-blowing sql
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL;

-- result:
-- +---------+--------+-------+
-- | article | dealer | price |
-- +---------+--------+-------+
-- |       1 | bill   |   101 |
-- |       2 | jeff   |    30 |
-- |       2 | bill   |    30 |
-- +---------+--------+-------+

-- find least expensive dealer via mind-blowing sql
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price > s2.price WHERE s2.article IS NULL;

-- result:
-- +---------+--------+-------+
-- | article | dealer | price |
-- +---------+--------+-------+
-- |       1 | jeff   |   100 |
-- |       2 | jeff   |    30 |
-- |       2 | bill   |    30 |
-- +---------+--------+-------+