OpenWritings.net

-- Create the table stock_prices.
CREATE TABLE stock_prices (
  symbol CHAR(10) NOT NULL,
  date DATE NOT NULL,
  price NUMERIC(12, 4) NOT NULL
);
 
-- Insert some values into stock_prices.
INSERT INTO stock_prices(symbol, date, price) VALUES('GOOG', '2008-01-01', '1.00');
INSERT INTO stock_prices(symbol, date, price) VALUES('GOOG', '2010-08-01', '1.20');
INSERT INTO stock_prices(symbol, date, price) VALUES('GOOG', '2009-07-01', '1.25');
 
INSERT INTO stock_prices(symbol, date, price) VALUES('IBM', '2008-03-01', '2.00');
INSERT INTO stock_prices(symbol, date, price) VALUES('IBM', '2010-02-01', '1.70');
INSERT INTO stock_prices(symbol, date, price) VALUES('IBM', '2009-09-08', '1.95');
 
INSERT INTO stock_prices(symbol, date, price) VALUES('YHOO', '2008-03-01', '2.01');
INSERT INTO stock_prices(symbol, date, price) VALUES('YHOO', '2010-08-01', '6.80');
INSERT INTO stock_prices(symbol, date, price) VALUES('YHOO', '2009-04-07', '3.95');
 
-- Show values that are being inserted.
SELECT * FROM stock_prices;
 
-- The meat here: You have to use LEFT JOIN to compare the price.
SELECT * FROM stock_prices 
LEFT JOIN stock_prices AS stock_prices2 
ON stock_prices.symbol = stock_prices2.symbol AND stock_prices.price < stock_prices2.price
WHERE stock_prices2.price IS NULL;

SELECT * FROM stock_prices;
+--------+------------+--------+
| symbol | date       | price  |
+--------+------------+--------+
| GOOG   | 2008-01-01 | 1.0000 |
| GOOG   | 2010-08-01 | 1.2000 |
| GOOG   | 2009-07-01 | 1.2500 |
| IBM    | 2008-03-01 | 2.0000 |
| IBM    | 2010-02-01 | 1.7000 |
| IBM    | 2009-09-08 | 1.9500 |
| YHOO   | 2008-03-01 | 2.0100 |
| YHOO   | 2010-08-01 | 6.8000 |
| YHOO   | 2009-04-07 | 3.9500 |
+--------+------------+--------+
 
SELECT * FROM stock_prices
LEFT JOIN stock_prices as stock_prices2
ON stock_prices.symbol = stock_prices2.symbol AND stock_prices.price < stock_prices2.price
WHERE stock_prices2.price is null;
+--------+------------+--------+--------+------+-------+
| symbol | date       | price  | symbol | date | price |
+--------+------------+--------+--------+------+-------+
| GOOG   | 2010-08-01 | 1.2000 | NULL   | NULL |  NULL |
| IBM    | 2010-02-01 | 1.7000 | NULL   | NULL |  NULL |
| YHOO   | 2010-08-01 | 6.8000 | NULL   | NULL |  NULL |
+--------+------------+--------+--------+------+-------+