• 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
    OCEAN User Group

    OCEAN TechCon25 Online

    It’s an Exciting Time for IBM i !

    July 16 & 17, 2025 – ONLINE

    Two virtual days of learning, presented by an outstanding group of IBM’ers and IBM Champions, featuring leading-edge topics.

    FREE for OCEAN members!

    Register NOW!

    Annual (12-month) Individual OCEAN Memberships are $80 and a Corporate Membership is $250. A Corporate Membership would allow your entire company to have full access to the OCEAN website & video library and to attend OCEAN events at member rates. Act now because rates are increasing on August 1, 2025.

    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

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

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 © 2025 IT Jungle