[SOLVED]Pulling duplicates

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

[SOLVED]Pulling duplicates

Post by evilmonkey »

Hello,

I need a query that will pull duplicates from a table set up like this:

FirstName || LastName || UserID

Since UserID cannot be duplicated (but sometimes is in my case), I need to know what those are so I can deal with them. The problem is, there are tonnes of ways to weed duplicated out of queries, but I have no idea how to structure a query to specifically show duplicates. I'm using JET (MS ACCESS), but SQL is SQL, so a point in the right direction would be helpful.

Thanks!
Last edited by evilmonkey on Fri Feb 16, 2007 1:12 pm, edited 1 time in total.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

try something like:

Code: Select all

SELECT * FROM table WHERE COUNT('UserID')>1 GROUP BY UserId
(untested, might not work at all, but it's a starting point)
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Sorry mate, your idea didn't work. I found this while scouring the internet:

Code: Select all

select UserID, count(UserID) from TableName
group by UserID
having count(*) > 1
Any comments? THe only problem is, I cannot select the rest of the rows (i.e. the FirstName and LastName) rows because Access complains about them now being in the count().
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

evilmonkey wrote:Sorry mate, your idea didn't work. I found this while scouring the internet:

Code: Select all

select UserID, count(UserID) from TableName
group by UserID
having count(*) > 1
Any comments?
Yes.
Change:

Code: Select all

count(*)
To:

Code: Select all

count(UserID)
I assume here that the UserID field is the primary key and therefore doing count(UserID) is much faster than count(*).
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

I did that, but UseerID is actually not a primary key. In the production world it will be (and won't be using access), but for now I'm trying to work through the problems in the data I was given. For instance, why the heck are there duplicate UserID's when UserID is suppossed to be (in the real world) primary. But yes, I did change as per your suggestion.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

evilmonkey wrote:but UseerID is actually not a primary key
evilmonkey wrote:why the heck are there duplicate UserID's
:? :? :?
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Okay, to avaoid confusion: I have data that needs to be migrated. Data has certain problems with it, including duplicate UserID's (I have no idea how that happenned, nor does it really concern me at this point). I have to find those problems and fix them to make sure that our techs can properly migrate the data.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

You havent said if the code change oren suggested worked.

It should it is valid SQL and is how I would search for duplicates.

Obviously UserID isnt a primary key, but that wont effect the query
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Oh...sorry. Yes, it worked. Thanks everyone. :)
Post Reply