Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    Oslo, Norway
    Posts
    2

    Unanswered: Simple SQL query

    Hi Gentlemen

    I'm a complete newbie to DB2, and using DB2 PE v7.2

    I have a dead simple question for You, to which I'm unable to find an elegant solution:

    I've simplified it down to

    C1 C2
    --- ----
    a1 b1
    a2 b1
    a2 b2
    a3 b1
    a4 b1
    a4 b2
    a4 b3

    I want to return only those C1 that cover all different values in C2, also a4. How is this done easily. in the most general form, because the original problem cover much larger tables. my play with all seems to only return empty set.

    Thanks


    Thanks

  2. #2
    Join Date
    Dec 2002
    Posts
    134

    Re: Simple SQL query

    if you know how many different values exist upfront:
    select c1
    from table1
    group by c1
    having count(distinct c2)=4

    If c2 is unique within c1, use count(*)=4
    If you do not know total number of distinct c2 - calculate it first :
    select max(cnt) from
    (select count(distinct c2) cnt
    from table1
    group by c1) a

    One of possible combine variants:

    with tmp (c1, cnt) as
    (select c1, count(distinct c2)
    from table1
    group by c1)
    select c1
    from tmp
    where cnt = (select max(cnt) from tmp)

Posting Permissions

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