468,792 Members | 1,725 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,792 developers. It's quick & easy.

Finding almost duplicate rows in mysql

I'm using MySQL 4.1.1

I've inherited a database which has some (almost) duplicate rows.

The databse is like this.

userID
userPosition
userDepartment

No user should be in more than one department but some are. I need to
find all the users who are listed in more than one department for manual
fixing.

I can do something like

SELECT *, COUNT(userID)
FROM users
GROUP BY COUNT(userID)
ORDER BY COUNT(userID) DESC

But that still gives me all 10,000 rows. All I want is where
COUNT(userID) > 1

If I add a WHERE clause to that effect I just get error messages.

Any suggestions?

Jul 17 '05 #1
4 11822
The query should be like this, I think:

SELECT userID, COUNT(*)
FROM users
GROUP BY userID
ORDER BY userID
HAVING COUNT(*) > 1;
Uzytkownik "Russell" <nu**@null.noemail> napisal w wiadomosci
news:DY*******************@news-binary.blueyonder.co.uk...
I'm using MySQL 4.1.1

I've inherited a database which has some (almost) duplicate rows.

The databse is like this.

userID
userPosition
userDepartment

No user should be in more than one department but some are. I need to
find all the users who are listed in more than one department for manual
fixing.

I can do something like

SELECT *, COUNT(userID)
FROM users
GROUP BY COUNT(userID)
ORDER BY COUNT(userID) DESC

But that still gives me all 10,000 rows. All I want is where
COUNT(userID) > 1

If I add a WHERE clause to that effect I just get error messages.

Any suggestions?

Jul 17 '05 #2
On 2004-01-10, Russell <nu**@null.noemail> wrote:
I'm using MySQL 4.1.1

I've inherited a database which has some (almost) duplicate rows.

The databse is like this.

userID
userPosition
userDepartment

No user should be in more than one department but some are. I need to
find all the users who are listed in more than one department for manual
fixing.

I can do something like

SELECT *, COUNT(userID)
FROM users
GROUP BY COUNT(userID)
ORDER BY COUNT(userID) DESC


That needs to be:
SELECT *
FROM users
GROUP BY userID HAVING COUNT(*) > 1
ORDER BY userID DESC
Oh, and a good database design requires you have a primary key for each
table. I suggest you use userID. This way, next time when another record
is inserted with a userID that already exists, mysql will complain about
that ;)
--
http://home.mysth.be/~timvw
Jul 17 '05 #3
Chung Leong wrote:
The query should be like this, I think:

SELECT userID, COUNT(*)
FROM users
GROUP BY userID
ORDER BY userID
HAVING COUNT(*) > 1;


You, Sir, are a star :) Thanks!

The HAVING clause has to come before ORDER BY but other than that it's
perfect - and yes, once the data is fixed, the Primary Key will be too.

Thanks,

Russell.

Jul 17 '05 #4

Hi,

try something like (not tested but it should be something like this) :

select
*
from
users u
where
(
(select count(*) from users where userID = u.userID) > 1
)

It might not be entirely correct but it should get you started.

Good luck !


On Sat, 10 Jan 2004 16:40:42 +0000, Russell <nu**@null.noemail> wrote:
I'm using MySQL 4.1.1

I've inherited a database which has some (almost) duplicate rows.

The databse is like this.

userID
userPosition
userDepartment

No user should be in more than one department but some are. I need to
find all the users who are listed in more than one department for manual
fixing.

I can do something like

SELECT *, COUNT(userID)
FROM users
GROUP BY COUNT(userID)
ORDER BY COUNT(userID) DESC

But that still gives me all 10,000 rows. All I want is where
COUNT(userID) > 1

If I add a WHERE clause to that effect I just get error messages.

Any suggestions?


--
Jeeke
Jul 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by g_chime | last post: by
1 post views Thread by TaeHo Yoo | last post: by
1 post views Thread by Asha | last post: by
7 posts views Thread by Jon Maz | last post: by
1 post views Thread by hung tran | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.