• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Obtain those values that are repeated in a field in a table

I have a sql server with something like this:
Field1   Field2
A             1
A             2
A             3
B              4
C              5
C              6
D              7
E              8
F              9
etc.

I need a query to obtain only those values in field1 that have repeated values. In this example: A and C
0
BemboX
Asked:
BemboX
  • 3
1 Solution
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
I did this quickly, but it should work foryou

SELECT a.Field1, a.Field2
  FROM myTable a
  JOIN  
(SELECT COUNT(*), Field1 FROM myTable GROUP BY Field1 HAVING COUNT(*) > 1) AS B
 ON B.Field1 = a.Field1
0
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
Actually, a slight error... this runs on SQL 2008

SELECT a.Field1, a.Field2
  FROM myTable a
  JOIN  
(SELECT COUNT(*) [knt], Field1 FROM myTable GROUP BY Field1 HAVING COUNT(*) > 1) AS B
 ON B.Field1 = a.Field1
0
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
and this will give you just field1 that has repeats

SELECT DISTINCT a.Field1
  FROM myTable a
  JOIN  
(SELECT COUNT(*) [knt], Field1 FROM myTable GROUP BY Field1 HAVING COUNT(*) > 1) AS B
 ON B.Field1 = a.Field1


wasn't sure if you wanted the values as well until re-reading your question
0
 
BemboXAuthor Commented:
Thanks!!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now