Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Mar 2012
    Posts
    27

    Unanswered: Why the error when using the 'MAX' function.

    Maybe I am doing something wrong here, or something I don't know about when using the 'MAX' function.

    Example:

    Correct - SELECT MAX(QUANTITY) FROM TABLE100

    Error - SELECT NAME, MAX(QUANTITY) FROM TABLE100

    Is there a way to display other columns and also show the maxium number for that record?

    Thx,

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Read about GROUP BY in the DB2 Infocenter version corresponding to your DB2 version,
    and also about other kinds of aggregation and OLAP features.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SELECT NAME, MAX(QUANTITY) FROM TABLE100
    What result do you want?
    Some examples would be desirable.

    Two possibilities may be suspected.
    (1) GROUP BY clause.
    (2) OLAP specification.

  4. #4
    Join Date
    Oct 2008
    Posts
    82

    Cool

    Try this ..

    SELECT NAME, MAX(QUANTITY) FROM TABLE100 Group by NAME

  5. #5
    Join Date
    Mar 2012
    Posts
    27
    Thanks guys for responding back so quickly. I was trying to make my example simple enough for what I thought I needed, however it just got complicated.

    Although you all anwered perfectly to what I was asking. The 'group by' worked as designed. My problem is that I gave a bad example.

    I might need to start a new forum because my question is now more advanced then what I asked originally.

    However, I will still explain my situation. My previous example I left out these columns 'customer number' , 'next payment', 'total paid', and the 'mode'. The 'mode' stands for which quarter of payment the customer is in. In which 00 means the beginning and 12 is the last.

    So the problem I am trying to solve is that I would like to pull the highest 'mode' that is where the function 'MAX' will be used, along with the values of 'next payment' and 'total paid'. Of course I have to 'GROUP' columns 'customer number', 'name', 'quantity', 'next payment', and 'total paid' which gives me in return all of the past 'mode'values, not just the one MAX(m0de) value.

    So my goal is to show only the highest 'mode' with 'next payment' and 'total paid' values for a particular customer.

    Any suggestions?
    Last edited by MrDatabase; 09-05-12 at 18:35.

  6. #6
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Suggest you show some sample data and what you want as the result when this sample data is processed.

  7. #7
    Join Date
    Mar 2012
    Posts
    27
    CustomerNum | Name | Product | Quantity | NextPayment | TotalPaid| Mode

    0004010 | Joe’s Glass House | BA | 3 | 300.00 | 100.00 | 00 |
    0004100 | Joe’s Glass House | BA | 0 | 200.00 | 100.00 | 01 |
    0004100 | Joe’s Glass House | SM | 1 | 60.00 | 20.00 | 00 |
    0004100 | Joe’s Glass House | SM | 0 | 40.00 | 20.00 | 01 |
    0004100 | Joe’s Glass House | WC | 5 | 1000.00 | 300.00 00 |
    0004100 | Joe’s Glass House | WC | 0 | 700.00 | 300.00 | 01 |


    I am looking for the highest value in the ‘mode’ column, in which the value can vary depending on the customer’s payment plan. So I cannot say something like this “mode > 0”.

    I would like to show only the rows that equal to the current value ‘01’ which is the highest value for this particular customer. This customer is on their second month of payment. Just remember this value for other customers can be anywhere between 00 to 12, it depends on the customer payment plan. This example below is what I am trying to display:

    CustomerNum | Name | Product | Quantity | NextPayment | TotalPaid | Mode

    0004100 | Joe’s Glass House | BA | 0 | 200.00 | 100.00 | 01 |
    0004100 | Joe’s Glass House | SM | 0 | 40.00 | 20.00 | 01 |
    0004100 | Joe’s Glass House | WC | 0 | 700.00 | 300.00 |01 |



    Can anyone help me?

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Just remember this value for other customers can be anywhere between 00 to 12, ...
    So, you should include such data in your sample data.

    And, it is better to supply sample data by CREAE TABLE statement and INSERT statement
    or by "WITH common-table-expression", like
    Code:
    WITH
      test_data
    ( Customer_Num , Name , Product , Quantity , Next_Payment , Total_Paid , Mode ) AS (
    VALUES
      ( '0004100' , 'Joe''s Glass House' , 'BA' , 3 ,  300.00 , 100.00 , '00' )
    , ( '0004100' , 'Joe''s Glass House' , 'BA' , 0 ,  200.00 , 100.00 , '01' )
    , ( '0004100' , 'Joe''s Glass House' , 'SM' , 1 ,   60.00 ,  20.00 , '00' )
    , ( '0004100' , 'Joe''s Glass House' , 'SM' , 0 ,   40.00 ,  20.00 , '01' )
    , ( '0004100' , 'Joe''s Glass House' , 'WC' , 5 , 1000.00 , 300.00 , '00' )
    , ( '0004100' , 'Joe''s Glass House' , 'WC' , 0 ,  700.00 , 300.00 , '01' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FA' , 3 ,  300.00 , 100.00 , '00' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FA' , 0 ,  200.00 , 100.00 , '01' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FA' , 0 ,  100.00 , 100.00 , '02' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FB' , 1 ,   80.00 ,  30.00 , '00' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FB' , 0 ,   30.00 ,  50.00 , '01' )
    , ( '0005020' , 'Alice Flower Shop ' , 'LC' , 5 , 1000.00 , 400.00 , '00' )
    , ( '0005020' , 'Alice Flower Shop ' , 'LC' , 0 ,  600.00 , 400.00 , '01' )
    , ( '0005020' , 'Alice Flower Shop ' , 'LC' , 3 ,  800.00 , 400.00 , '02' )
    )
    SELECT * FROM  test_data;
    ------------------------------------------------------------------------------
    
    CUSTOMER_NUM NAME               PRODUCT QUANTITY    NEXT_PAYMENT TOTAL_PAID MODE
    ------------ ------------------ ------- ----------- ------------ ---------- ----
    0004100      Joe's Glass House  BA                3       300.00     100.00 00  
    0004100      Joe's Glass House  BA                0       200.00     100.00 01  
    0004100      Joe's Glass House  SM                1        60.00      20.00 00  
    0004100      Joe's Glass House  SM                0        40.00      20.00 01  
    0004100      Joe's Glass House  WC                5      1000.00     300.00 00  
    0004100      Joe's Glass House  WC                0       700.00     300.00 01  
    0005020      Alice Flower Shop  FA                3       300.00     100.00 00  
    0005020      Alice Flower Shop  FA                0       200.00     100.00 01  
    0005020      Alice Flower Shop  FA                0       100.00     100.00 02  
    0005020      Alice Flower Shop  FB                1        80.00      30.00 00  
    0005020      Alice Flower Shop  FB                0        30.00      50.00 01  
    0005020      Alice Flower Shop  LC                5      1000.00     400.00 00  
    0005020      Alice Flower Shop  LC                0       600.00     400.00 01  
    0005020      Alice Flower Shop  LC                3       800.00     400.00 02
    So my goal is to show only the highest 'mode' with 'next payment' and 'total paid' values for a particular customer.
    Is your required result from above sample data like this?
    Code:
    CUSTOMER_NUM NAME               PRODUCT QUANTITY    NEXT_PAYMENT TOTAL_PAID MODE
    ------------ ------------------ ------- ----------- ------------ ---------- ----
    0004100      Joe's Glass House  BA                0       200.00     100.00 01  
    0004100      Joe's Glass House  SM                0        40.00      20.00 01  
    0004100      Joe's Glass House  WC                0       700.00     300.00 01  
    0005020      Alice Flower Shop  FA                0       100.00     100.00 02  
    0005020      Alice Flower Shop  LC                3       800.00     400.00 02
    or, this?
    Code:
    CUSTOMER_NUM NAME               NEXT_PAYMENT TOTAL_PAID MODE
    ------------ ------------------ ------------ ---------- ----
    0004100      Joe's Glass House        200.00     100.00 01  
    0004100      Joe's Glass House         40.00      20.00 01  
    0004100      Joe's Glass House        700.00     300.00 01  
    0005020      Alice Flower Shop        100.00     100.00 02  
    0005020      Alice Flower Shop        800.00     400.00 02

  9. #9
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by MrDatabase View Post
    CustomerNum | Name | Product | Quantity | NextPayment | TotalPaid| Mode

    0004010 | Joe’s Glass House | BA | 3 | 300.00 | 100.00 | 00 |
    0004100 | Joe’s Glass House | BA | 0 | 200.00 | 100.00 | 01 |
    0004100 | Joe’s Glass House | SM | 1 | 60.00 | 20.00 | 00 |
    0004100 | Joe’s Glass House | SM | 0 | 40.00 | 20.00 | 01 |
    0004100 | Joe’s Glass House | WC | 5 | 1000.00 | 300.00 00 |
    0004100 | Joe’s Glass House | WC | 0 | 700.00 | 300.00 | 01 |


    I am looking for the highest value in the ‘mode’ column, in which the value can vary depending on the customer’s payment plan. So I cannot say something like this “mode > 0”.

    I would like to show only the rows that equal to the current value ‘01’ which is the highest value for this particular customer. This customer is on their second month of payment. Just remember this value for other customers can be anywhere between 00 to 12, it depends on the customer payment plan. This example below is what I am trying to display:

    CustomerNum | Name | Product | Quantity | NextPayment | TotalPaid | Mode

    0004100 | Joe’s Glass House | BA | 0 | 200.00 | 100.00 | 01 |
    0004100 | Joe’s Glass House | SM | 0 | 40.00 | 20.00 | 01 |
    0004100 | Joe’s Glass House | WC | 0 | 700.00 | 300.00 |01 |



    Can anyone help me?
    Not sure I fully understood you problem, but here's a - untested - starter

    Code:
    select T.*
    from (
          select CustomerNum, Product, max(mode) as max_mode
          from T
          group by CustomerNum, Product
    ) X
    join T
        on  (T.CustomerNum, T.Product, T.mode)
          = (X.CustomerNum, X.Product, X.max_mode
    If this is correct it can be optimized using OLAP functions.

    See Tonkumas post for info on how to post sample data. Info on keys is also useful for those trying to help.
    --
    Lennart

  10. #10
    Join Date
    Mar 2012
    Posts
    27
    Thanks tonkuma, and Lellel2 for the help.

    My apologies for not placing my sample data the correct way, I hope it wasn’t too confusing.

    Now I know the correct way thanks to tonkuma instructions.

    But to answer tonkuma’s question, yes the first table result with columns customer_num, name, product, quantity, next_payment, total_paid, and mode is what I am looking for as a result.

    How did you do it?

  11. #11
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by MrDatabase View Post
    Thanks tonkuma, and Lellel2 for the help.

    My apologies for not placing my sample data the correct way, I hope it wasn’t too confusing.

    Now I know the correct way thanks to tonkuma instructions.

    But to answer tonkuma’s question, yes the first table result with columns customer_num, name, product, quantity, next_payment, total_paid, and mode is what I am looking for as a result.

    How did you do it?
    Is it max(mode) per customer_num regardless of product? Try

    Code:
    WITH T
    ( Customer_Num , Name , Product , Quantity , Next_Payment , Total_Paid , Mode ) AS (
    VALUES
      ( '0004100' , 'Joe''s Glass House' , 'BA' , 3 ,  300.00 , 100.00 , '00' )
    , ( '0004100' , 'Joe''s Glass House' , 'BA' , 0 ,  200.00 , 100.00 , '01' )
    , ( '0004100' , 'Joe''s Glass House' , 'SM' , 1 ,   60.00 ,  20.00 , '00' )
    , ( '0004100' , 'Joe''s Glass House' , 'SM' , 0 ,   40.00 ,  20.00 , '01' )
    , ( '0004100' , 'Joe''s Glass House' , 'WC' , 5 , 1000.00 , 300.00 , '00' )
    , ( '0004100' , 'Joe''s Glass House' , 'WC' , 0 ,  700.00 , 300.00 , '01' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FA' , 3 ,  300.00 , 100.00 , '00' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FA' , 0 ,  200.00 , 100.00 , '01' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FA' , 0 ,  100.00 , 100.00 , '02' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FB' , 1 ,   80.00 ,  30.00 , '00' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FB' , 0 ,   30.00 ,  50.00 , '01' )
    , ( '0005020' , 'Alice Flower Shop ' , 'LC' , 5 , 1000.00 , 400.00 , '00' )
    , ( '0005020' , 'Alice Flower Shop ' , 'LC' , 0 ,  600.00 , 400.00 , '01' )
    , ( '0005020' , 'Alice Flower Shop ' , 'LC' , 3 ,  800.00 , 400.00 , '02' )
    ) 
    select T.*
    from (
          select Customer_Num, max(mode) as max_mode
          from T
          group by Customer_Num
    ) X
    join T
        on  (T.Customer_Num, T.mode)
          = (X.Customer_Num, X.max_mode);
    
    CUSTOMER_NUM NAME               PRODUCT QUANTITY    NEXT_PAYMENT TOTAL_PAID MODE
    ------------ ------------------ ------- ----------- ------------ ---------- ----
    0004100      Joe's Glass House  BA                0       200.00     100.00 01  
    0004100      Joe's Glass House  SM                0        40.00      20.00 01  
    0004100      Joe's Glass House  WC                0       700.00     300.00 01  
    0005020      Alice Flower Shop  FA                0       100.00     100.00 02  
    0005020      Alice Flower Shop  LC                3       800.00     400.00 02  
    
      5 record(s) selected.
    --
    Lennart

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by lelle12 View Post
    ...

    If this is correct it can be optimized using OLAP functions.

    ...
    Here is an example of using an OLAP function.

    Note: Underline was used to separate words in compound words(Customer_Num , Next_Payment , Total_Paid).
    Code:
    SELECT Customer_Num , Name , Product , Quantity , Next_Payment , Total_Paid , Mode
     FROM (SELECT t.*
                , MAX(mode)
                     OVER( PARTITION BY Customer_Num ) AS max_mode
            FROM  /*test_data*/ t
          )
     WHERE mode = max_mode
     ORDER BY
           Customer_Num
         , Product
    ;
    Last edited by tonkuma; 09-06-12 at 11:15.

  13. #13
    Join Date
    Mar 2012
    Posts
    27
    Hello lelle12,

    Yes I would like max(mode) per customer_num regardless of product, but with a parameter that represents the customer_num. I assume that an extra WHERE clause will work.

    Now please forgive me for asking a silly question here regarding the code you have supplied me.

    I want to make sure that I translate this code right, because I am pulling data from a large table, not a table created with fix values.

    So here is my example from your code:

    SELECT Customer_Num , Name , Product , Quantity , Next_Payment , Total_Paid , Mode FROM (SELECT tt.*, MAX(mode) OVER(PARTITION BY Customer_Num) As max_mode FROM TABLE100 As tt)
    WHERE mode = max_mode
    AND Customer_Num = ?
    ORDER BY Customer_Num, Product

    Is this right when using a non-static table?

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    with a parameter that represents the customer_num
    Specify the customer_num in subquery.
    And, "PARTITION BY Customer_Num" is not necessary.
    Code:
    WITH
      TABLE100
    ( Customer_Num , Name , Product , Quantity , Next_Payment , Total_Paid , Mode ) AS (
    VALUES
      ( '0004100' , 'Joe''s Glass House' , 'BA' , 3 ,  300.00 , 100.00 , '00' )
    , ( '0004100' , 'Joe''s Glass House' , 'BA' , 0 ,  200.00 , 100.00 , '01' )
    , ( '0004100' , 'Joe''s Glass House' , 'SM' , 1 ,   60.00 ,  20.00 , '00' )
    , ( '0004100' , 'Joe''s Glass House' , 'SM' , 0 ,   40.00 ,  20.00 , '01' )
    , ( '0004100' , 'Joe''s Glass House' , 'WC' , 5 , 1000.00 , 300.00 , '00' )
    , ( '0004100' , 'Joe''s Glass House' , 'WC' , 0 ,  700.00 , 300.00 , '01' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FA' , 3 ,  300.00 , 100.00 , '00' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FA' , 0 ,  200.00 , 100.00 , '01' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FA' , 0 ,  100.00 , 100.00 , '02' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FB' , 1 ,   80.00 ,  30.00 , '00' )
    , ( '0005020' , 'Alice Flower Shop ' , 'FB' , 0 ,   30.00 ,  50.00 , '01' )
    , ( '0005020' , 'Alice Flower Shop ' , 'LC' , 5 , 1000.00 , 400.00 , '00' )
    , ( '0005020' , 'Alice Flower Shop ' , 'LC' , 0 ,  600.00 , 400.00 , '01' )
    , ( '0005020' , 'Alice Flower Shop ' , 'LC' , 3 ,  800.00 , 400.00 , '02' )
    )
    SELECT Customer_Num , Name , Product , Quantity , Next_Payment , Total_Paid , Mode
     FROM (SELECT t.*
                , MAX(mode) OVER() AS max_mode
            FROM  TABLE100 AS t
            WHERE Customer_Num = '0005020'
          )
     WHERE mode = max_mode
     ORDER BY
           Customer_Num
         , Product
    ;
    ------------------------------------------------------------------------------
    
    CUSTOMER_NUM NAME               PRODUCT QUANTITY    NEXT_PAYMENT TOTAL_PAID MODE
    ------------ ------------------ ------- ----------- ------------ ---------- ----
    0005020      Alice Flower Shop  FA                0       100.00     100.00 02  
    0005020      Alice Flower Shop  LC                3       800.00     400.00 02  
    
      2 record(s) selected.

  15. #15
    Join Date
    Mar 2012
    Posts
    27
    SELECT Customer_Num , Name , Product , Quantity , Next_Payment , Total_Paid , Mode
    FROM (SELECT t.*
    , MAX(mode) OVER() AS max_mode
    FROM TABLE100 AS t
    WHERE Customer_Num = '0005020'
    )
    WHERE mode = max_mode
    ORDER BY
    Customer_Num
    , Product

    Hmm... I am receiving a '[SQL0104] Token ( was not valid. Valid tokens: , FROM INTO.' error message when running it.

Tags for this Thread

Posting Permissions

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