Finding maximum number of duplicate records in Access Table?

Dear members,
Here is the sample of my Access Table

ID SecondaryID
1 101
1 101
1 102
1 103
1 103
1 103
2 104
2 105
2 105
3 106
3 107
3 108
3 108
3 108
3 108


I want to do is get unique ID having greatest number of duplicate SecondaryID…So that my table becomes in this form


ID SecondaryID
1 103
2 105
3 108

Please suggest a query for me....thanx:confused:

OK, here's what I came up with... first the whole code

Code:

---

DECLARE @MyTable TABLE (ID INT NOT NULL, SecondaryID INT NOT NULL)
INSERT INTO @MyTable (ID, SecondaryID) Values(1,101)
INSERT INTO @MyTable (ID, SecondaryID) Values(1,101)
INSERT INTO @MyTable (ID, SecondaryID) Values(1,102)
INSERT INTO @MyTable (ID, SecondaryID) Values(1,103)
INSERT INTO @MyTable (ID, SecondaryID) Values(1,103)
INSERT INTO @MyTable (ID, SecondaryID) Values(1,103)

INSERT INTO @MyTable (ID, SecondaryID) Values(2,103)
INSERT INTO @MyTable (ID, SecondaryID) Values(2,105)
INSERT INTO @MyTable (ID, SecondaryID) Values(2,105)

INSERT INTO @MyTable (ID, SecondaryID) Values(3,106)
INSERT INTO @MyTable (ID, SecondaryID) Values(3,107)
INSERT INTO @MyTable (ID, SecondaryID) Values(3,108)
INSERT INTO @MyTable (ID, SecondaryID) Values(3,108)
INSERT INTO @MyTable (ID, SecondaryID) Values(3,108)
INSERT INTO @MyTable (ID, SecondaryID) Values(3,108)


SELECT C.ID, C.SecondaryID
FROM (SELECT ID, SecondaryID, COUNT(*) as RCount
                        FROM @MyTable
                        GROUP BY ID, SecondaryID) C
INNER JOIN (SELECT ID, MAX(RCount) AS RCount
                        FROM (SELECT ID, SecondaryID, COUNT(*) as RCount
                                                FROM @MyTable
                                                GROUP BY ID, SecondaryID) C2
                        GROUP BY ID) T
        ON T.ID = C.ID
                AND T.RCount = C.RCount
ORDER BY C.ID

---

IT was done with SQL Server, but the query itself is simple SQL, so it should still work for you...

this is the part that you're going to want:

Code:

---

SELECT C.ID, C.SecondaryID
FROM (SELECT ID, SecondaryID, COUNT(*) as RCount
                        FROM @MyTable
                        GROUP BY ID, SecondaryID) C
INNER JOIN (SELECT ID, MAX(RCount) AS RCount
                        FROM (SELECT ID, SecondaryID, COUNT(*) as RCount
                                                FROM @MyTable
                                                GROUP BY ID, SecondaryID) C2
                        GROUP BY ID) T
        ON T.ID = C.ID
                AND T.RCount = C.RCount
ORDER BY C.ID

---

You'll need to change the table name... but that should work for you.

The only time you may run into a problem is when you have multiple SecondaryIDs with the same count...

-tg

Try this:

Code:

---

SELECT ID, MAX(SecondaryID), MAX(IDCount) FROM (SELECT ID, SecondaryID, Count(*) as IDCount from num group by ID, SecondaryID) GROUP BY ID

---

MarMan - I thought of that too... except that if there were a line for ID = 1, Secondary ID = 104 .... the max(SEcondaryID) will return 104, but the count would be from 103... which is incorrect.

And just to make sure I wasn't crazy... that's what I did... added an additional ID =1 line with 104 as the Secondary ID....
Here are the results:
my query

Code:

---

/*
ID                  SecondaryID       
----------------------------------------
*/
1                  103               
2                  105               
3                  108

---

Yours:

Code:

---

/*
ID                  Column1            Column2           
------------------------------------------------------------
*/
1                  104                3                 
2                  105                2                 
3                  108                4                 


/*(3 row(s) affected)*/

---

Complete Table Listing:

Code:

---


/*
ID                  SecondaryID       
----------------------------------------
*/
1                  101               
1                  101               
1                  102               
1                  103               
1                  103               
1                  103               
1                  104               
2                  104               
2                  105               
2                  105               
3                  106               
3                  107               
3                  108               
3                  108               
3                  108               
3                  108               


/*(16 row(s) affected)*/

---

Other than that, given the data we were supplied, your query works fine ... I usually get hung up in those kinds of details because it's my job to think about things like that.

-tg

Thanks for the info tech, you are right, I just tested it with the supplied data.

This forum will help my improve my SQL! :)

Thanks techgnome and Marman for the reply...i am sorry if i have been not so clear in asking my question

Tech, i am going through your code and i will reply soon...


Thanx....:bigyello:

Code:

---

SELECT C.ID, C.SecondaryID
FROM (SELECT ID, SecondaryID, COUNT(*) as RCount
                        FROM @MyTable
                        GROUP BY ID, SecondaryID) C
INNER JOIN (SELECT ID, MAX(RCount) AS RCount
                        FROM (SELECT ID, SecondaryID, COUNT(*) as RCount
                                                FROM @MyTable
                                                GROUP BY ID, SecondaryID) C2
                        GROUP BY ID) T
        ON T.ID = C.ID
                AND T.RCount = C.RCount
ORDER BY C.ID

---

Thanx again Techgnome,

It solved my problem...::thumb: