Help wit Query to list duplicates

I am trying to create a list of those cases where a specific field value was
duplicate with a set of rows. The query I am trying is shown below (it is
listing all rows instead of just those where there is a duplicate value in
the field Eqp1Voc.
ID is an unique key for each row.
What did I overlook?
Wayne
========================================
=
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE ID IN
(SELECT B.ID
FROM DCScores A JOIN DCScores B
ON A.ID <> B.ID
AND A.Eqp1Voc = B.Eqp1Voc)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
========================================
=> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
(SELECT B.ID
FROM DCScores
Group by B.ID
HAVING COUNT(Eqp1Voc) >1)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
>I am trying to create a list of those cases where a specific field value
>was
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>|||Thanks for the quick response but that doesn't return any rows (and I know
there are duplicates in that field!) With your suggestion, the query now
looks like this:
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE ID IN
(SELECT B.ID
FROM DCScores B
Group by B.ID
HAVING COUNT(Eqp1Voc) >1)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%238nw0CEQFHA.688@.TK2MSFTNGP14.phx.gbl...
> (SELECT B.ID
> FROM DCScores
> Group by B.ID
> HAVING COUNT(Eqp1Voc) >1)
> HTH, Jens Smeyer
> --
> http://www.sqlserver2005.de
> --
>
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
is
in
>|||Sorry for that missed up something...
Here it goes:
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE Eqp1Voc IN
(SELECT Eqp1Voc
FROM DCScores
Group by Eqp1Voc
HAVING COUNT(Eqp1Voc) > 1)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
Jens.
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
>I am trying to create a list of those cases where a specific field value
>was
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>|||Jens;
Thanks again but with that change, it returns all rows, not just those with
a duplicate value?
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23FGm0YEQFHA.204@.TK2MSFTNGP15.phx.gbl...
> Sorry for that missed up something...
> Here it goes:
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE Eqp1Voc IN
> (SELECT Eqp1Voc
> FROM DCScores
> Group by Eqp1Voc
> HAVING COUNT(Eqp1Voc) > 1)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
>
> Jens.
>
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
is
in
>|||Try this:
SELECT UnitName, Eqp1Judge,
Eqp1Voc
FROM DCScores S
Where Exists
(Select * From DCScores
Where Eqp1Voc = S.Eqp1Voc
Group By Eqp1Voc
Having Count(*) > 1)
WHERE CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
"Wayne Wengert" wrote:

> I am trying to create a list of those cases where a specific field value w
as
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>
>|||Thanks for the suggestion but that yields an error: "Incorrect syntax near
the keyword 'WHERE'."
Wayne
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:F52016E3-1BE3-48A4-9A3D-193DA51896CD@.microsoft.com...
> Try this:
> SELECT UnitName, Eqp1Judge,
> Eqp1Voc
> FROM DCScores S
> Where Exists
> (Select * From DCScores
> Where Eqp1Voc = S.Eqp1Voc
> Group By Eqp1Voc
> Having Count(*) > 1)
> WHERE CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
>
> "Wayne Wengert" wrote:
>
was
is
in|||Replace this "WHERE CircuitID" WITH "AND CircuitID"
Jens Smeyer.
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:u7e0HAFQFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Thanks for the suggestion but that yields an error: "Incorrect syntax near
> the keyword 'WHERE'."
> Wayne
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:F52016E3-1BE3-48A4-9A3D-193DA51896CD@.microsoft.com...
> was
> is
> in
>|||Jens;
I took that advice and also realized that the Where clause needs to be
applied to that inner select. I changed it to the following and now it
works.
I appreciate all the help.
==================================
SELECT UnitName, Eqp1Judge,
Eqp1Voc
FROM DCScores S
Where Exists
(Select * From DCScores
Where Eqp1Voc = S.Eqp1Voc
AND CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Group By Eqp1Voc
Having Count(*) > 1)
AND CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
==================================
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23KCoZCFQFHA.2380@.TK2MSFTNGP10.phx.gbl...
> Replace this "WHERE CircuitID" WITH "AND CircuitID"
> Jens Smeyer.
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:u7e0HAFQFHA.1176@.TK2MSFTNGP12.phx.gbl...
near
(it
value
>