Results 1 to 5 of 5

Thread: SQL help

  1. #1
    Join Date
    Nov 2011
    Posts
    8

    Unanswered: SQL help

    We have the following "Orders" table:
    O_Id OrderDate OrderPrice Customer
    1 2008/11/12 1000 Hansen
    2 2008/10/23 1600 Nilsen
    3 2008/09/02 700 Hansen
    4 2008/09/03 300 Hansen
    5 2008/08/30 2000 Jensen
    6 2008/10/04 100 Nilsen

    Now we want to find the largest value of the "OrderPrice" column.

    We use the following SQL statement:
    SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

    Along with it we also want which day it happened.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see "Retrieving MAXimum row" in "SQL on Fire! Part 1"
    http://sirdug.org/downloads/SQLonFire_1_SirDUG.pdf

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    SELECT A.OrderPrice, A.OrderDate FROM Orders A
    where A.OrderPrice = (SELECT MAX(OrderPrice) FROM Orders B);

    Note that it possible that you could retrieve multiple rows for the above query if the MAX(OrderPrice) was a tie.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Nov 2011
    Posts
    8
    Thank you so much Marcus_A It worked as I expected.

    tonkuma ,

    Thanks for the link. It was helpful. Reading the document.
    Last edited by edoo.okati; 11-16-11 at 08:17. Reason: typos

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another way is to use OLAP specification.

    If you want to get only one row, use ROW_NUMBER().
    If you want to get all rows having MAX(OrderPrice), use RANK() or DENSE_RANK().


    Example 1: Returned one row. Added a row(o_id = 7).
    Note 1: It may be better to use underscore("_") to separate words in a compoubd word, than use of initial capital letter.
    Because, SQL is case insensitive.

    Note 2: Date format 'yyyy/mm/dd' is not supported globally in DB2. It must be local format.
    So, I changed your sample data to ISO format 'yyyy-mm-dd'.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH Orders(O_Id , Order_Date , Order_Price , Customer) AS (
    VALUES
      ( 1 , DATE('2008-11-12') , 1000 , 'Hansen' )
    , ( 2 , DATE('2008-10-23') , 1600 , 'Nilsen' )
    , ( 3 , DATE('2008-09-02') ,  700 , 'Hansen' )
    , ( 4 , DATE('2008-09-03') ,  300 , 'Hansen' )
    , ( 5 , DATE('2008-08-30') , 2000 , 'Jensen' )
    , ( 6 , DATE('2008-10-04') ,  100 , 'Nilsen' )
    , ( 7 , DATE('2008-12-18') , 2000 , 'Nilsen' )
    )
    SELECT O_Id , Order_Date
         , Order_Price AS Largest_Order_Price
         , Customer
     FROM  (SELECT od.*
                 , ROW_NUMBER() OVER(ORDER BY Order_Price DESC) rnum
             FROM  Orders AS od
           ) od
     WHERE rnum = 1
    ;
    ------------------------------------------------------------------------------
    
    O_ID        ORDER_DATE LARGEST_ORDER_PRICE CUSTOMER
    ----------- ---------- ------------------- --------
              5 2008-08-30                2000 Jensen  
    
      1 record(s) selected.
    Example 2: Returned two rows.
    Code:
    /* Same data as Example 1. */
    SELECT O_Id , Order_Date
         , Order_Price AS Largest_Order_Price
         , Customer
     FROM  (SELECT od.*
                 , RANK() OVER(ORDER BY Order_Price DESC) rank
             FROM  Orders AS od
           ) od
     WHERE rank = 1
    ;
    ------------------------------------------------------------------------------
    
    O_ID        ORDER_DATE LARGEST_ORDER_PRICE CUSTOMER
    ----------- ---------- ------------------- --------
              5 2008-08-30                2000 Jensen  
              7 2008-12-18                2000 Nilsen  
    
      2 record(s) selected.
    Last edited by tonkuma; 11-15-11 at 16:51. Reason: Add " AS Largest_Order_Price".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •