Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2013
    Posts
    3

    Unanswered: Find Duplicate rows

    Hi
    I have a table that has duplicate values in two columns

    ColA ColB ColC
    101 Mar 2 2013 12:05AM flag1
    102 Mar 2 2013 12:05AM flag1
    101 Mar 3 2013 4:45AM Flag2
    103 Mar 3 2013 4:45AM Flag2
    104 Mar 3 2013 4:45AM Flag2
    102 Mar 3 2013 4:45AM Flag2
    104 Mar 3 2013 4:45AM Flag2
    105 Mar 3 2013 4:45AM Flag2
    104 Mar 3 2013 4:46AM Flag2
    105 Mar 3 2013 4:46AM Flag2


    I need a query to get the following

    ColA ColB ColC Dup Count
    101 Mar 2 2013 12:05AM flag1 2
    102 Mar 2 2013 12:05AM flag1 6
    104 Mar 3 2013 4:46AM Flag2 2

    Thank you

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    1) For 101 you result use the minimum date on ColB and for 104 the max date. Which one do you want to see, the min or the max value?
    2) Your test data show 2 occurrences of 102, how do you get to a count of 6
    3) Your test data show 3 occurrences of 104, how do you get to a count of 2
    4) Why do you exclude 105

  3. #3
    Join Date
    Mar 2013
    Posts
    3
    Thank you for the quick response

    The Dup count is based on total number of duplicates in ColC when the date and time are same in ColB.

    There are two duplicates in ColC with the same date/time "Mar 2 2013 12:05AM"
    101 Mar 2 2013 12:05AM flag1
    102 Mar 2 2013 12:05AM flag1

    There are six duplicates in ColC with the same date/time "Mar 3 2013 4:45AM"
    101 Mar 3 2013 4:45AM Flag2
    103 Mar 3 2013 4:45AM Flag2
    104 Mar 3 2013 4:45AM Flag2
    102 Mar 3 2013 4:45AM Flag2
    104 Mar 3 2013 4:45AM Flag2
    105 Mar 3 2013 4:45AM Flag2


    thank you

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    For the data given, there is no way to get the desired result. (I should be careful about using absolutes because somebody may prove it wrong!)

    Look at your data:

    Code:
    ColA       ColB       ColC
    101 Mar 3 2013 4:45AM Flag2
    103 Mar 3 2013 4:45AM Flag2
    104 Mar 3 2013 4:45AM Flag2
    102 Mar 3 2013 4:45AM Flag2
    104 Mar 3 2013 4:45AM Flag2
    105 Mar 3 2013 4:45AM Flag2
    Now look at your desired output:

    Code:
    102 Mar 2 2013 12:05AM flag1 6
    You have codes 101, 102, 103, 104, & 105 in ColA. How do you determine that you want 102 for ColA output?

    If you can live without ColA, this query will get you started:

    Code:
    Select ColB, ColC, COUNT(*) as DupCount
      from testD
      group by ColB, ColC
    If you must have something in ColA, you could add a MIN or MAX to get the smallest or largest ColA that is associated with each grouping:

    Code:
    Select MIN(ColA), ColB, ColC, COUNT(*) as DupCount
      from testD
      group by ColB, ColC
    HTH

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by ajmiester03 View Post
    The Dup count is based on total number of duplicates in ColC when the date and time are same in ColB.
    Then why does your desired result in post1 show 2 rows with a count of 2 and a count of 6 for the same ColC=flag1 and colB=Mar 2 2013 12:05AM
    Quote Originally Posted by ajmiester03 View Post
    I need a query to get the following

    ColA ColB ColC Dup Count
    101 Mar 2 2013 12:05AM flag1 2
    102 Mar 2 2013 12:05AM flag1 6
    104 Mar 3 2013 4:46AM Flag2 2
    Assuming LinksUp guessed correct as to what you really want, Add this to the end of the query
    Code:
    having count(*)>1

  6. #6
    Join Date
    Mar 2013
    Posts
    3

    Thank you

    pdreyer/Linksup,


    Thank you. That was helpful. Unfortunately I am having issues getting that result with data in a SYBASE 12 tables while it works in SQL 2008. I will have to check why.

  7. #7
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Not sure why it won't work in Sybase.

    There is nothing tricky about the code that was posted. It is all Standard SQL.

Posting Permissions

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