• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Which One Is the Culprit?

    August 8, 2007 Hey, Ted

    I am using the MAX function in a SQL statement to get the maximum value from a few columns. I don’t know which element is producing the maximum value. Is there a way to identify the element that has the maximum value?

    –Hisham

    Yes, Hisham. Thanks for allowing me another opportunity to show off that most versatile of SQL features: CASE.

    For the benefit of the readers, here’s the SQL statement Hisham sent to me.

    SELECT max(WK01, WK02, WK03, WK04) FROM SALES
    

    Here’s the sort of results his SQL query generates.

     
    MAX 
    ===
    100 
    120 
    150
    

    He has the maximum value from each input row (record), but how does he know which column (field) had the maximum value? Like this:

    SELECT MAX(wk01, wk02, wk03, wk04), 
     CASE WHEN wk01 = MAX(wk01, wk02, wk03, wk04) THEN 1 
          WHEN wk02 = MAX(wk01, wk02, wk03, wk04) THEN 2 
          WHEN wk03 = MAX(wk01, wk02, wk03, wk04) THEN 3 
          WHEN wk04 = MAX(wk01, wk02, wk03, wk04) THEN 4 
     END AS MaxPos 
    FROM SomeTable
    

    In case of a tie, the CASE expression returns the position of the first one.

    Suppose the input data looks like this:

    Wk01   Wk02   Wk03   Wk04
    ====   ====   ====   ====
      25    100     75     80  
     120    100     55    110
     120    150    150     90
    

    Now the results look like this:

     
    MAX     MaxPos 
    ===     ======
    100       2
    120       1
    150       2
    

    CASE is a marvelous tool. I find new uses for it often, and we’ve presented several examples of the use of CASE in previous editions of Four Hundred Guru.

    –Ted



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    RPG & DB2 Summit

    Summit Hands-On Live! Workshop Series
    April 27 – May 20, 2021

    In-Person, Interactive, In-Depth — Online!

    Master critical IBM i development skills at record speed with the Summit’s full-day Hands-On Live! workshops.

    Teaching live online via video meeting, instructors Paul Tuohy, Jon Paris, Susan Gantner, and Mike Pavlak take you deep into the best ways to use your favorite technologies, cementing your knowledge with guided, hands-on labs and personal attention.

    Choose from 7 different workshops to hone your skills.

    • SQL Procedures, Functions & Triggers
    • Intermediate SQL for RPG Developers
    • RPG Procedures & Service Programs
    • Building Modern RPG Applications (2-day workshop!)
    • RDi Quick Start
    • RDi Beyond the Basics
    • Python for RPGers

    Check out the workshop abstracts and prepare to take your productivity to new heights!

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    DRV Technologies:  Automatically convert and distribute AS/400 reports with SpoolFlex
    Maximum Availability:  The ultimate System i replication for business of all sizes
    COMMON:  Join us at the Annual 2008 conference, March 30 - April 3, in Nashville, Tennessee

    IT Jungle Store Top Book Picks

    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    Cape Clear Tests New ESB Release Power6-Based System i Performance: Your Mileage Will Vary

    Leave a Reply Cancel reply

Volume 7, Number 29 -- August 8, 2007
THIS ISSUE SPONSORED BY:

Help/Systems
ProData Computer Services
Guild Companies

Table of Contents

  • Let WDSc Help You Format Your Source Code
  • Which One Is the Culprit?
  • Admin Alert: Eliminating Easy-to-Guess User Passwords

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Need An RPG Programmer? Nalashaa May Have You Covered
  • Every Day Has To Be Earth Day
  • Guru: Compare Pieces Of Source Members
  • As I See It: Ambivalence
  • IBM i PTF Guide, Volume 23, Number 16
  • Query Supervisor Gives Database Engineers New Power
  • IBM Unveils New and Improved IBM i Services
  • 3 Takeaways from the 2021 PowerTech Security Report
  • Four Hundred Monitor, April 14
  • IBM i PTF Guide, Volume 23, Number 15

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2021 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.