Finding maximum number of duplicate records in Access Table?

[imstillalive]

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

[techgnome]

OK, here's what I came up with... first the whole 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:

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

[MarMan]

Try this:

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

[techgnome]

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

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

Yours:

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


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

Complete Table Listing:

/*
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

[MarMan]

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

This forum will help my improve my SQL!

[imstillalive]

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....

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

[imstillalive]

Thanx again Techgnome,

It solved my problem...: