Results 1 to 8 of 8

Thread: SQL Help

  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Unanswered: SQL Help

    Table T1
    C1 C2 C3 C4 C5.............
    --------------------------------------
    A 1 N
    B 1 Y
    B 1 N
    B 2 N

    I have this above table.
    For every entry with C3 = 'Y', there will be a C3 = 'N'.
    I want to write a query which selects all the records ignoring the records with C3 = 'N' when there is an entry for 'Y'.

    Expected RESULT:

    C1 C2 C3 C4 C5.............
    --------------------------------------
    A 1 N
    B 1 Y
    B 2 N

    I am using a DB2 database.

    Thanks,
    Raja

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My question is what values did you want for C4, C5, so on...

    If I ignore those other columns and only focused on C1, C2 and C3,
    it would be easy by grouping C1 and C2 then take max of C3, like...
    Code:
    SELECT c1 , c2
         , MAX(c3) AS c3
     FROM  t1
     GROUP BY
           c1 , c2
    So, please publish more sample data(rows and columns) including normal and exceptional data and expected results from the data,
    if you want to see more close response for your requirements.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example which considering other columns might be like...

    Code:
    SELECT c1 , c2 , c3 , c4 , c5 , ...
     FROM (SELECT t1.*
                , ROW_NUMBER() OVER(PARTITION BY c1 , c2
                                        ORDER BY c3 DESC) AS rn
            FROM  t1
          )
     WHERE rn = 1
    ;

  4. #4
    Join Date
    Mar 2012
    Posts
    4
    Hi,

    Thanks much for your help. But the max(c3) as C3 is not working.
    I tried to give more inputs on this. Please check

    C1 C2 C3 C4 C5.............
    --------------------------------------
    A 1 N
    B 1 Y
    B 1 N
    B 2 N
    C 1 N
    C 4 Y
    C 4 N
    D 7 N
    E 6 Y
    E 6 N
    _________________________

    Hope this will do...

  5. #5
    Join Date
    Mar 2012
    Posts
    4
    Desired output

    C1 C2 C3 C4 C5.............
    --------------------------------------
    A 1 N
    B 1 Y
    B 2 N
    C 1 N
    C 4 Y
    D 7 N
    E 6 Y
    _________________________

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The result of my trial was here.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      T1(c1 , c2 , c3) AS (
    VALUES
      ( 'A' , 1 , 'N' )
    , ( 'B' , 1 , 'Y' )
    , ( 'B' , 1 , 'N' )
    , ( 'B' , 2 , 'N' )
    , ( 'C' , 1 , 'N' )
    , ( 'C' , 4 , 'Y' )
    , ( 'C' , 4 , 'N' )
    , ( 'D' , 7 , 'N' )
    , ( 'E' , 6 , 'Y' )
    , ( 'E' , 6 , 'N' )
    )
    SELECT c1 , c2
         , MAX(c3) AS c3
     FROM  t1
     GROUP BY
           c1 , c2
    ;
    ------------------------------------------------------------------------------
    
    C1 C2          C3
    -- ----------- --
    A            1 N 
    B            1 Y 
    B            2 N 
    C            1 N 
    C            4 Y 
    D            7 N 
    E            6 Y 
    
      7 record(s) selected.
    Isn't it what you wanted?
    I want to write a query which selects all the records ignoring the records with C3 = 'N' when there is an entry for 'Y'.
    If the result of me was not your required result,
    please publish your expected results from the data.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Or, change datatype of C2 to varchar, like...
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      T1(c1 , c2 , c3) AS (
    VALUES
      ( 'A' , '1' , 'N' )
    , ( 'B' , '1' , 'Y' )
    , ( 'B' , '1' , 'N' )
    , ( 'B' , '2' , 'N' )
    , ( 'C' , '1' , 'N' )
    , ( 'C' , '4' , 'Y' )
    , ( 'C' , '4' , 'N' )
    , ( 'D' , '7' , 'N' )
    , ( 'E' , '6' , 'Y' )
    , ( 'E' , '6' , 'N' )
    )
    SELECT c1 , c2
         , MAX(c3) AS c3
     FROM  t1
     GROUP BY
           c1 , c2
    ;
    ------------------------------------------------------------------------------
    
    C1 C2 C3
    -- -- --
    A  1  N 
    B  1  Y 
    B  2  N 
    C  1  N 
    C  4  Y 
    D  7  N 
    E  6  Y 
    
      7 record(s) selected.
    But, the values of C3 were same as my previous result and your expected result.

  8. #8
    Join Date
    Mar 2012
    Posts
    4

    Thanks you

    Thanks very much...
    I see what you are saying now...
    Its working

Posting Permissions

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