DB2 max() query

puneet_raj · #1 (**permalink**) 08-14-11, 18:22

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.

puneet_raj · #2 (**permalink**) 08-14-11, 18:27

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)

tonkuma · #3 (**permalink**) 08-14-11, 19:04

Your requirements were not clear for me.
You wrote

Quote:

How to get 2 or more max() output ...

But, I saw only one max() in your queries.

Quote:

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.

Quote:

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.

Stealth_DBA · #4 (**permalink**) 08-14-11, 21:23

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.

tonkuma · #5 (**permalink**) 08-14-11, 22:02

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.

puneet_raj · #6 (**permalink**) 08-15-11, 12:10

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,

puneet_raj · #7 (**permalink**) 08-15-11, 12:21

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

tonkuma · #8 (**permalink**) 08-15-11, 23:26

puneet_raj,

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

1)

Quote:

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)

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech