473,218 Members | 1,322 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,218 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 1633
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

77
by: nospam | last post by:
Reasons for a 3-tier achitecture for the WEB? (NOTE: I said, WEB, NOT WINDOWS. DON'T shoot your mouth off if you don't understand the difference.) I hear only one reason and that's to switch a...
3
by: dp | last post by:
I am new to VB.NET and I have a simple question. How do I show a form from a command button click event? The code I have below is not working. I am trying to show the form frmAgent. What am I...
2
by: Ajai Kumar .R | last post by:
Hai all, I've two or more forms on my app. My requirement is, Have to show the first form asa the user press a button have to hide the first form and show the second form. If the user press the...
0
by: Efkas | last post by:
I have a full custom application with some widged extending Controls like Label and PictureBox. I build a menu with these widgets. When I click on one of them, it calls a function to display...
12
by: google_groups3 | last post by:
Hi all. I currently have 2 text files which contain lists of file names. These text files are updated by my code. What I want to do is be able to merge these text files discarding the...
1
by: gdarian216 | last post by:
I am tring to get rid of dupes and his code is taking the first input and repeating it. I don't know why. this is what i have so far can anyone help #include <iostream> using namespace std; ...
4
by: kdubble | last post by:
Hi I am trying to get the results of a query to show only unique student records (not duplicates). Is there a simple way to make the criteria field do this? I am not too familiar with SQL. ...
7
by: Jan | last post by:
Hi: When I searched the newsgroup for this problem, I saw two or three instances of the question being asked, but it was never answered. Not too promising, but here goes: I have a form with...
1
oranoos3000
by: oranoos3000 | last post by:
hi would you please help me i have a online shopping center that i show pictures of the my product in home page. in the InterExplorer pictures is shown correctly but in Firefox browser is shown...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.