Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Mar 25, 1997

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
71062.1056@compuserve.com

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date