Finding almost duplicate rows in mysql

[Russell]

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?

[Chung Leong]

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?

[Tim Van Wassenhove]

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

[Russell]

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.

[Jeeke]

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