Finding the Column Within a Row with the Least Value

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]

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may

man on floor with data

DevX Quick Guide to Data Ingestion

One of the biggest trends of the 21st century is the massive surge in internet usage. With major innovations such as smart technology, social media, and online shopping sites, the internet has become an essential part of everyday life for a large portion of the population. Due to this internet

payment via phone

7 Ways Technology Has Changed Traditional Payments

In today’s digital world, technology has changed how we make payments. From contactless cards to mobile wallets, it’s now easier to pay for goods and services without carrying cash or using a checkbook. This article will look at seven of the most significant ways technology has transformed traditional payment methods.