Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2013
    Posts
    4

    Unanswered: Checking multiple records in DB2 table

    Hi friends,

    I'm new to dbforums and sql... I need a sql query with following conditions

    Suppose there is a table, TAB1, having 3 columns( Col1, Col2, Col3)

    col1 can be any alphanumeric value.
    Col2 can be one of SE, M, GM
    Col3 can be one of BANG, CHN, DEL, GUR, HYD
    I need to find out duplicates in Col1 with the following conditions

    1) Col2 should be always 'SE'
    2) Whenever col1 has duplicates and col2 is 'SE', if the corresponding col3 values are also equal for that particular multiple value in col1 then i need to display duplicate col1 values.

    Example:-

    Col1 | Col2 | Col3
    ---- | ----- | ------
    A13 | SE | BANG
    A13 | SE | BANG

    B14 | SE | CHN
    B14 | SE | CHN
    B14 | GM | AHM

    C15 | SE | HYD
    C15 | SE | GUR

    D16 | SE | DEL
    D16 | GM | DEL


    For this table I need to display A13,B14


    Can anyone please help me with the query friends
    Last edited by Mr.Protagonist; 03-08-13 at 02:13.

  2. #2
    Join Date
    Mar 2013
    Location
    Breda, Netherlands
    Posts
    15
    Code:
    SELECT col1, col2, col3, COUNT(*) amount
    FROM table
    GROUP BY col1, col2, col3
    HAVING COUNT(*) > 1

  3. #3
    Join Date
    Mar 2013
    Posts
    4
    Hi Gervs,

    Thanks for the rep. But the is not working. My main requirment is that I should be checking only for 'SE' of 'Col2'. So " WHERE Col2= 'SE' " should be included in the the query.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Is it working when you added the where clause????

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Apr 2004
    Posts
    3
    Please try this

    SELECT col1, col2, col3 FROM
    ( select col1,col2,col3, row_number() over(partition by col1,col2,col3) as rn
    from table
    where col1='SE'
    )
    where rn=1;

  6. #6
    Join Date
    Mar 2013
    Posts
    4
    Hi jitendra...

    The query is not working... There is a syntax error...

  7. #7
    Join Date
    Apr 2012
    Posts
    156
    Hi, I took a look and by adding the where clause to the solution provided by GERVS this works without any issues:
    SELECT col1, col2, col3, COUNT(*) amount
    FROM test
    where col2 = 'SE'
    GROUP BY col1, col2, col3 HAVING COUNT(*) > 1

    If this gives a syntax issue, repost with what the error is you are getting. I think GERVS assumed you would know to just add a where clause.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Mr.Protagonist View Post
    ...
    ...

    I need to find out duplicates in Col1 with the following conditions

    1) Col2 should be always 'SE'
    2) Whenever col1 has duplicates and col2 is 'SE', if the corresponding col3 values are also equal for that particular multiple value in col1
    then i need to display duplicate col1 values.

    For this table I need to display A13,B14

    ...
    If I understand the requirements words by words,
    those examples might help you.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     tab1
    ( col1 , col2 , col3 ) AS (
    VALUES
      ( 'A13' , 'SE' , 'BANG' )
    , ( 'A13' , 'SE' , 'BANG' )
    , ( 'B14' , 'SE' , 'CHN'  )
    , ( 'B14' , 'SE' , 'CHN'  )
    , ( 'B14' , 'GM' , 'AHM'  )
    , ( 'C15' , 'SE' , 'HYD'  )
    , ( 'C15' , 'SE' , 'GUR'  )
    , ( 'D16' , 'SE' , 'DEL'  )
    , ( 'D16' , 'GM' , 'DEL'  )
    )
    SELECT col1
     FROM  tab1
     WHERE col2 = 'SE'
     GROUP BY
           col1 , col3
     HAVING
           COUNT(*) > 1
    ;
    ------------------------------------------------------------------------------
    
    COL1
    ----
    A13 
    B14 
    
      2 record(s) selected.

    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     tab1
    ( col1 , col2 , col3 ) AS (
    VALUES
      ( 'A13' , 'SE' , 'BANG' )
    , ( 'A13' , 'SE' , 'BANG' )
    , ( 'B14' , 'SE' , 'CHN'  )
    , ( 'B14' , 'SE' , 'CHN'  )
    , ( 'B14' , 'GM' , 'AHM'  )
    , ( 'C15' , 'SE' , 'HYD'  )
    , ( 'C15' , 'SE' , 'GUR'  )
    , ( 'D16' , 'SE' , 'DEL'  )
    , ( 'D16' , 'GM' , 'DEL'  )
    )
    SELECT col1
     FROM  tab1
     WHERE col2 = 'SE'
     GROUP BY
           col1
     HAVING
           COUNT(*)  > 1
       AND MAX(col3) = MIN(col3)
    ;
    ------------------------------------------------------------------------------
    
    COL1
    ----
    A13 
    B14 
    
      2 record(s) selected.
    Last edited by tonkuma; 03-10-13 at 05:16.

Posting Permissions

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