Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: Strange SQL Query

    I have a table that looks like this:
    Code:
    A | B | C | D
    --------------
    0 | 1 | 4 | 6
    0 | 3 | 2 | 3
    1 | 1 | 4 | 6
    0 | 3 | 3 | 4
    If a record has the same values in column B, C and D as another record, I only want to return the one in which column A has a value of 1.
    I also want to return all records that have distinct values of B, C and D. So, in this case, I'd want to return all but the first row.
    How do I write a query that actually accomplishes that, though?

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    something like this ?

    Code:
    select *
    from table1
    where a = 1 and (b = c) and ( d <> c)
    
    union all
    select * from
    (select a, b, c, d
    from table1
    where (b <> c) and ( c <> d)
    fetch first row only ) aa

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by VectorX7 View Post
    If a record has the same values in column B, C and D as another record, I only want to return the one in which column A has a value of 1.
    I also want to return all records that have distinct values of B, C and D.
    If I understand this correctly, you want to group by (B,C,D), then show all groups of one line, and show the "A=1" line of all groups of more than 1 line, if present, correct?
    In that case I would write:
    Code:
    SELECT max(A),B,C,D
    FROM my_table
    GROUP BY B,C,D
    HAVING count(*) = 1
    UNION ALL
    SELECT 1,B,C,D
    FROM my_table
    HAVING count(*) > 1 AND count(CASE A WHEN 1 THEN 1 END) > 0
    If there is a guarantee that all groups of more than one row have a row with A=1, and moreover all values of A are nonnegative, this would simplify to
    Code:
    SELECT abs(min(CASE A WHEN 1 THEN -1 ELSE A END)),B,C,D
    FROM my_table 
    GROUP BY B,C,D
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think both conditions can be put in one HAVING clause.

    Example 1:
    Code:
    SELECT CASE COUNT(*)
           WHEN 1 THEN
                MAX(a)
           ELSE 1
           END  AS a
         , b , c , d
     FROM  a_table
     GROUP BY
           b , c , d
     HAVING
           COUNT(*) = 1
       OR  MAX( CASE a WHEN 1 THEN 1 ELSE 0 END ) = 1
    ;

    Example 2:
    Code:
    SELECT CASE COUNT(*)
           WHEN 1 THEN
                MAX(a)
           ELSE 1
           END  AS a
         , b , c , d
     FROM  a_table
     GROUP BY
           b , c , d
     HAVING
           1 IN ( COUNT(*) , MAX( INT(COS(a - 1)) ) )
    ;

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The CASE expression for column A in Example 1 and 2 can be replaced by another expression without using CASE expression.
    Because, COUNT(*) is always positive integer.

    Example 3:
    Code:
    SELECT 1 / COUNT(*) * MAX(a - 1) + 1 AS a
         , b , c , d
     FROM  a_table
     GROUP BY
           b , c , d
     HAVING
           1 IN ( COUNT(*) , MAX( INT(COS(a - 1)) ) )
    ;
    Note: consider order of operators and result of integer division.
    1) order of operators
    1 / COUNT(*) * MAX(a - 1) + 1
    is equivalent to
    ( ( 1 / COUNT(*) ) * MAX(a - 1) ) + 1

    2) result of integer division
    If COUNT(*) = 1, then ( 1 / COUNT(*) ) = 1
    If COUNT(*) > 1, then ( 1 / COUNT(*) ) = 0
    Last edited by tonkuma; 10-22-11 at 15:38. Reason: Add Note.

Posting Permissions

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