Re: Finding almost duplicate rows in mysql

From: Chung Leong (chernyshevsky_at_hotmail.com)
Date: 01/10/04 

Date: Sat, 10 Jan 2004 12:13:42 -0500

The query should be like this, I think:

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

Uzytkownik "Russell" <null@null.noemail> napisal w wiadomosci
news:DYVLb.11333$6L3.10494@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?
>

Relevant Pages

  • Re: [Q]Problems related to the MySQL linked Server.
    ... The error messge when I query data with following SQL. ... select @v_intUserNo = uno ... where userid = 'testid' ... I have two questions about the linked server for the MySQL. ...
    (microsoft.public.sqlserver.odbc)
  • Finding almost duplicate rows in mysql
    ... I've inherited a database which has some duplicate rows. ... userID ...
    (comp.lang.php)
  • RE: Exporting to Excel
    ... GetUserId ... Static Function GetUserIdAs String ... 'Open the specific query with the data to be exported ... You can use this to drive a loop that will export a worksheet per userid. ...
    (microsoft.public.access.externaldata)
  • RE: Exporting to Excel
    ... I did make the Criteria for the patientid field as GetUserId() not GetUserId. ... Static Function GetUserIdAs String ... 'Open the specific query with the data to be exported ... You can use this to drive a loop that will export a worksheet per userid. ...
    (microsoft.public.access.externaldata)
  • RE: Conditional display of records ...
    ... So in my query, I will map 'A' and 'B' for flag='Y' and userid (with which ... users logon) with the userid stored in table 'B'. ... The trick is to create a function that calls the username function and pass ... Function CheckFlag(strFlag As String) As Boolean ...
    (microsoft.public.access.formscoding)