CODEWITHSUNDEEP
c#sqldatabasems-accessdatatable
Hassen
Hassen

Reputation: 870

Querying a database or a datatable for duplicate records in one column

On MS access, how to get the "ID" of the records having duplicate content on the "myData" column ?

something like : 

----------------------             ------------------------         
  ID   |   myData    |             |   ID   |   myData    |   
----------------------             ------------------------
   1   |   AAA       |             |    1   |   AAA       |
----------------------             ------------------------
   2   |   BBB       |             |    5   |   AAA       |
----------------------    ==>      ------------------------
   3   |   CCC       |             |    2   |   BBB       |
----------------------             ------------------------
   4   |   BBB       |             |    4   |   BBB       |
----------------------             ------------------------
   5   |   AAA       |
----------------------

All I can do so far do is this query:

SELECT        myData, COUNT(myData) AS Expr1
FROM            fooDB
GROUP BY myData
HAVING        (COUNT(myData) > 1)

which only returns a list of the duplicates records from "mydata" and the number of occurrences, adding anything else will fail at execute. (and no ID's)

OR

Saying I accessing the DB as a DataTable in C#, how to manage this? Especially when this table has ~2000 records.

( maybe some help on how to work with INTERSECT to let it return the full rows having duplicates on one column )

Thanks.

Upvotes: 3

Views: 857

Answers (4)

dotjoe
dotjoe

Reputation: 26940

Just throwing this out there...

SELECT distinct
    f.ID,
    f.myData
FROM 
    fooDB f
    inner join fooDB f2 on f.myData = f2.myData
        and f.ID <> f2.ID

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Try

SELECT ID
  FROM fooDB
  WHERE myData IN (SELECT myData
                     FROM (SELECT myData, COUNT(myData) AS ROW_COUNT
                             FROM fooDB
                             GROUP BY myData)
                     WHERE ROW_COUNT > 1)

Share and enjoy.

Upvotes: 0

Justin K
Justin K

Reputation: 2694

SELECT ID, fooDB.myData
FROM (
  SELECT myData
  FROM fooDB
  GROUP BY myData
  HAVING COUNT(myData) > 1
) t INNER JOIN fooDB ON (t.myData = fooDB.myData)

Upvotes: 2

Tom H
Tom H

Reputation: 47464

I don't know if you can do a subquery in Access like this, but here's a typical SQL way to do it:

SELECT
    id,
    my_data
FROM
    My_Table
WHERE
    my_data IN
    (
        SELECT
            my_data
        FROM
            My_Table
        GROUP BY
            my_data
        HAVING
            COUNT(*) > 1
    )

Upvotes: 1

Related Questions