Results 1 to 8 of 8

Thread: DB2 max() query

  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Exclamation Unanswered: DB2 max() query

    How to get 2 or more max() output from a single query. I mean I dont want 1st and 2nd highest value. I have a query something like:

    select max(col1) from tab1 where col2 in ( select col2 from tab2)

    My subquery(select col2 from tab2) returns more than 1 values. I need max() values of all results of the subquery.

  2. #2
    Join Date
    Aug 2011
    Posts
    4
    I tried the correlated query something like this but in vain..pls help.

    select max(col1) from tab1 where col2 in (select col2 in tab2 where tab1.col2=tab2.col2)

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your requirements were not clear for me.
    You wrote
    How to get 2 or more max() output ...
    But, I saw only one max() in your queries.

    I mean I dont want 1st and 2nd highest value.
    But, a max() function gives you only one highest value.

    max(<expression-1>, max(<expression-2>, ... in a query return one corresponding results for each max() function.

    My subquery(select col2 from tab2) returns more than 1 values. I need max() values of all results of the subquery.
    ... max() values of all results of ...
    Why plural?
    Anyhow, if you wneed a max() value, you can use simply a MAX function, like ...
    (select MAX(col2) from tab2)


    Showing some sample data and expected result from the data would be better to clarify your requirement.
    If you described your requirement with more phrases and sentences,
    it would be not easy to understand for other peoples including me.
    Last edited by tonkuma; 08-14-11 at 22:23. Reason: Add third question.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    puneet_raj, You are missing a GROUP BY clasue:
    Code:
    WITH TAB1(COL1, COL2)
      AS (
          SELECT  1, 'A' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT  2, 'A' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT  3, 'A' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT  4, 'B' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT  5, 'B' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT  6, 'B' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT  7, 'C' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT  8, 'C' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT  9, 'C' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 10, 'D' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 11, 'D' FROM SYSIBM.SYSDUMMY1
         )
        , TAB2 (COL2)
      AS (
          SELECT 'A' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'C' FROM SYSIBM.SYSDUMMY1
         )
    SELECT MAX(COL1) AS MAX_COL1
    FROM TAB1
    WHERE EXISTS (
                  SELECT *
                  FROM TAB2
                  WHERE TAB1.COL2 = TAB2.COL2
                 )
    GROUP BY COL2
    
    MAX_COL1   
    -----------
              3
              9
    
      2 record(s) selected.
    3 is the MAX value for COL2 'A' values and 9 is the MAX value for COL2 'C' value. COL2 values 'B' and 'D' are not in TAB2 so do not show up.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It might be better to include col2 into final result, like...
    Code:
    SELECT col2
         , MAX(COL1) AS MAX_COL1
     FROM  TAB1
     WHERE EXISTS
           (SELECT *
             FROM  TAB2
             WHERE TAB1.COL2 = TAB2.COL2
           )
     GROUP BY
           COL2
    ;
    ------------------------------------------------------------------------------
    
    COL2 MAX_COL1   
    ---- -----------
    A              3
    C              9
    
      2 record(s) selected.

  6. #6
    Join Date
    Aug 2011
    Posts
    4

    Smile

    Thanks Stealth_DBA !!!
    Yes you are right, adding group by clause worked wonders to me. Thanks again for explaining the things with beautiful example.
    Hi Tonkuma,
    yes agreed including col2 in result would make things clear and help verify my results, the question I have asked here was a small part of my even bigger sql query and there I need to get only max() value .

    Anyways Thanks Guys,


  7. #7
    Join Date
    Aug 2011
    Posts
    4
    I added group by clause to my query like this:

    select max(col1)
    from tab1
    where col2 in (select col2 in tab2 where tab1.col2=tab2.col2)
    group by col2

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    puneet_raj,

    I'm curious to know the followings questions.
    Would you mind to answer my questions?

    1)
    select max(col1)
    from tab1
    where col2 in (select col2 in tab2 where tab1.col2=tab2.col2)
    group by col2
    Why did you used IN predicate than shorter EXISTS predicate(like an example of Stealth_DBA)?

    2)
    the question I have asked here was a small part of my even bigger sql query and there I need to get only max() value.
    How the query was used in the bigger sql query?
    Or, can you show me the bigger sql query itself?
    The reason of this question is that I couldn't imagine the use of max values only without combination of grouping column.

Posting Permissions

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