Question:
(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.00

he wanted this result:

    Result    item   lowprice    ======================    001     12.50     002     10.95    003     12.50    004      0.00

Mr. 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?

Answer:
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:
Joe Celko
[email protected]