# Thread: Find Duplicate rows

1. Registered User
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. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
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. Registered User
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. Registered User
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. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
Originally Posted by ajmiester03
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
Originally Posted by ajmiester03
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. Registered User
Join Date
Mar 2013
Posts
3

## Thank you

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. Registered User
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
•