(Joe Celko’s Least Little Thing Puzzle)
Gregg Reno asked for an answer to a “Simple SQL puzzle” on the SQL Roundtable section of the DBMS CompuServe forum in March.
He wanted to find an SQL statement that would give the lower of the two values in the same row. His example was something like this:
CREATE TABLE PriceList (item INTEGER NOT NULL PRIMARY KEY, price1 DECIMAL (8,2) NOT NULL, price2 DECIMAL (8,2) NOT NULL, … );Given this data:
PriceList item price1 price2 ====================== 001 12.50 17.95 002 17.50 10.95 003 12.50 12.50 004 12.50 0.00he wanted this result:
Result item lowprice ====================== 001 12.50 002 10.95 003 12.50 004 0.00Mr. Reno was used to Oracle SQL which has a function “Least (col1, col2, …colN)” that returns the smallest value in a list. But he was working with a new product which had not implemented this feature and this threw him off. How many different ways can you find to attack the problem?
First of all, you might be able to declare your table with a CHECK() clause to guarantee which of the two price columns will be the lowest one:
CREATE TABLE PriceList_1 (item INTEGER NOT NULL PRIMARY KEY, price1 DECIMAL (8,2) NOT NULL, price2 DECIMAL (8,2) NOT NULL CHECK (price1 <= price2), ... );But this might not be possible because the columns could represent different sources of information and not be interchangeable.
You could normalize the table to replace the two columns with a single price column and source column, thus:
CREATE TABLE PriceList_2 (item INTEGER NOT NULL PRIMARY KEY, price DECIMAL (8,2) NOT NULL, source INTEGER NOT NULL CHECK (source IN (1,2)), … UNIQUE (item, source), … );Then the query is quite easy:
SELECT item, MIN(price) AS lowprice FROM PriceList_2 GROUP BY item;In the current SQL-92 standard, you can use the original table and a CASE expression:
SELECT item, (CASE WHEN price1 < price2 THEN price1 ELSE price2 END) AS lowprice FROM PriceList;
Puzzle provided courtesy of: