How do I query to find duplicates

[LyndonOHRC]

I have a table with two columns:

Table: RaceStock
Columns: HorseName, Breed

The customer says the HorseName can be duplicate if breed is diferent.

The table is huge so I need an efficient query that gives me HorseName and Breed where HorseName occurs on more than one row. I guess it is the opposite of a SELECT DISTINCT.

I'm stumped; any help is appreciated.

Thanks
Lyndon

 

[lespaul]

SELECT HorseName, Count(*) FROM RaceStock
GROUP BY HorseName
HAVING Count(*) > 1

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[LyndonOHRC]

Thanks Leslie, that gives me my Horse Names but no breed column that I will need for my output.

Any ideas?

Lyndon

 

[lespaul]

SELECT HorseName, Breed FROM RaceStock
GROUP BY HorseName
HAVING Count(HorseName) > 1

 

[LyndonOHRC]

That gives a "Syntax Error in Group By Clause"

 

[PHV]

SELECT A.HorseName, A.Breed
FROM RaceStock AS A INNER JOIN (
SELECT HorseName FROM RaceStock GROUP BY HorseName HAVING Count(*)>1
) AS G ON A.HorseName = G.HorseName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886