Select Duplicate Values

37Seconds · #1 (**permalink**) 09-23-09, 16:41

I am trying to select duplicate values for a field in a table. The table looks like the following:

Code:

OID   Product    Serial    Status
1     100          abcd        3
2     100          abcf        4
3     100          abcd        3
4     101          ebcd        3
5     101          ebcf        4
6     101          ebcd        3

I would like my result set to be like the following:

Code:

Product    Serial    Status    Count
100          abcd       3       2
101          abcd       3       2

Any ideas on how to get these results?

n_i · #2 (**permalink**) 09-23-09, 16:55

I'm sure the query would include the GROUP BY and HAVING clauses. You can find samples in the manual or in the wonderful SQL Cookbook by Graeme Birchall, which is available for free download.

Lenny77 · #3 (**permalink**) 09-23-09, 18:04

Quote:

Originally Posted by 37Seconds

I am trying to select duplicate values for a field in a table. The table looks like the following:

Code:

OID   Product    Serial    Status
1     100          abcd        3
2     100          abcf        4
3     100          abcd        3
4     101          ebcd        3
5     101          ebcf        4
6     101          ebcd        3

I would like my result set to be like the following:

Code:

Product    Serial    Status    Count
100          abcd       3       2
101          abcd       3       2

Any ideas on how to get these results?

Code:

Select Product, Serial, Status, count(*) count
from table1
group by Product, Serial, Status
having count(*) > 1

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech