Hi all - I have a table (DataTab) see ex. below.
_ID ssn
cell 123456789
cell 123456789
cell 123456789
cell 321000000
cell 321000000
cell 654000000
screen_2 654000000
screen_2 654000000
screen_2 654000000
screen_2 987000000
screen_2 987000000
screen_2 987000000
screen_2 987654321
screen_2 987654321
I would like to return the top 2 count of SSNs by ID (see expected results below).
ID ssn Count
cell 123456789 3
cell 321000000 2
screen_2 654000000 3
screen_2 987000000 3
I have the SQL below but when I run it I am asked to enter the paramter values for T2.CountID and I can't figure out why.
Code:
SELECT T1.*
FROM DataTab as T1
WHERE T1.[_ID] IN (
SELECT TOP 2 Count(T2.[_ID]) as CountID
FROM DataTab as T2
WHERE T2.SSN = T1.SSN
ORDER BY T2.CountID DESC)
ORDER BY SSN, [_ID];