Page 1 of 1
[SOLVED]Pulling duplicates
Posted: Wed Feb 14, 2007 1:00 pm
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!
Posted: Wed Feb 14, 2007 3:01 pm
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)
Posted: Thu Feb 15, 2007 10:19 am
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().
Posted: Thu Feb 15, 2007 10:31 am
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:
To:
I assume here that the
UserID field is the primary key and therefore doing
count(UserID) is much faster than
count(*).
Posted: Thu Feb 15, 2007 10:40 am
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.
Posted: Thu Feb 15, 2007 11:27 am
by Oren
evilmonkey wrote:but UseerID is actually not a primary key
evilmonkey wrote:why the heck are there duplicate UserID's

Posted: Thu Feb 15, 2007 1:10 pm
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.
Posted: Thu Feb 15, 2007 2:48 pm
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
Posted: Fri Feb 16, 2007 1:12 pm
by evilmonkey
Oh...sorry. Yes, it worked. Thanks everyone.
