471,579 Members | 1,700 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,579 software developers and data experts.

Show dupes

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
3 1515
SELECT * FROM TABLE1 where Field2 IN (SELECT FIELD2 FROM TABLE1 GROUP
BY Field2 HAVING Count(*) > 1)

Apr 24 '06 #2
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
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.

Similar topics

3 posts views Thread by dp | last post: by
2 posts views Thread by Ajai Kumar .R | last post: by
12 posts views Thread by google_groups3 | last post: by
1 post views Thread by gdarian216 | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by Vinnie | last post: by
1 post views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.