SQL Access query VBA: how to gather the record that has the max nbr of subscriptions-CodePudding

This is my data:

id	key	nbr of subs
1	ABC	10
1	XXX	3
2	MNO	120
3	ABC	5
3	FGH	110

I need the key for the record (ID) that has the max nbr of subscriptions:

id	key	nbr of subs
1	ABC	10
2	MNO	120
3	FGH	110

I don't mind deleting the extra records, or electing the ones I need and insert them into other table. Any ideas?

SELECT P.Key, MAX(P.[Nbr of Subcriptions]) 
FROM P
GROUP BY P.Key;

Thank you very much

CodePudding user response：

You need correlated subquery. Try below SQL-

SELECT t1.ID, t1.Key, t1.NBR FROM Table1 as t1 
    INNER JOIN (SELECT Table1.ID, Max(Table1.NBR) AS MaxOfNBR
FROM Table1 GROUP BY Table1.ID) as t2 ON (t1.NBR = t2.MaxOfNBR) AND (t1.ID = t2.ID);