By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

Show dupes

P: n/a
Hi.

This issue semms trivial, but I didn't get it working so far.

I have a database, which contains dupes. I'd like to create a query,
which shows all dupes (not only one record, but all records which are
double).

E.g.

01.02.2006 fanta ...
03.02.2006 fanta ...
08.02.2006 fanta ...
01.03.2006 coke ...
02.03.2006 coke ...
09.03.2006 pepsi ...
09.03.2006 bud ...
09.03.2006 sprite ...

The result should be ...

01.02.2006 fanta ...
03.02.2006 fanta ...
08.02.2006 fanta ...
01.03.2006 coke ...
02.03.2006 coke ...

How can I do this?

Thanks,

--
Georges Heinesch
Apr 24 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
SELECT * FROM TABLE1 where Field2 IN (SELECT FIELD2 FROM TABLE1 GROUP
BY Field2 HAVING Count(*) > 1)

Apr 24 '06 #2

P: n/a
ar********@gmail.com wrote:
SELECT * FROM TABLE1 where Field2 IN (SELECT FIELD2 FROM TABLE1 GROUP
BY Field2 HAVING Count(*) > 1)


Perfect !!!

Thanks a lot!

--
Georges Heinesch
Apr 25 '06 #3

P: n/a
ar********@gmail.com wrote:
SELECT * FROM TABLE1 where Field2 IN (SELECT FIELD2 FROM TABLE1 GROUP
BY Field2 HAVING Count(*) > 1)


I've just recently started playing with QA's execution plan feature and
this is interesting to me. I came up with the following SQL statement
to solve the OP's problem:

SELECT T.ColaDate, T.ColaName
FROM dbo.Table1 T
INNER JOIN
(
SELECT ColaName AS CountedName, COUNT(ColaName) AS ColaNameCount
FROM Table1
GROUP BY ColaName
) AS Q
ON T.ColaName = Q.CountedName
WHERE (Q.ColaNameCount > 1)
ORDER BY ColaDate

The execution plan for this query includes a sort after each of the
indexed clustered scans which your query doesn't include. Are the sorts
something required by the JOIN?

As a general rule would it be preferred to avoid using a JOIN for
performance reasons if you can use the IN keyword?
May 3 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.