Select 10 Records per Operator ID

  • Thread starter Thread starter blackdoggiegirl
  • Start date Start date
B

blackdoggiegirl

I am trying to read a file that has operator ids and account numbers. My
assignment is to get the first 10 or less for each operator and skip the rest
and go on to the next operator. I got the code to work for less than 10 but
not when there are more than 10 accounts. Thanks for your help. The code is
below.

SELECT MyFile.OPR_ID, MyFile.ACCT_NBR, COUNT(Aug_Eff_Non_Blocks.ACCT_NBR) AS
NUM_ACCTS
FROM MyFile
GROUP BY MyFile.OPR_ID, MyFile.ACCT_NBR
HAVING COUNT(MyFile.ACCT_NBR) <= 10
ORDER BY MyFile.OPR_ID, MyFile.ACCT_NBR DESC;
 
To return 10 or less records per operator, you might try the following SQL.

SELECT Distinct MyFile.OPR_ID
, MyFile.ACCT_NBR
FROM MyFile
WHERE MyFile.Acct_NBr IN
(SELECT Top 10 M.Acct_Nbr
FROM MyFile as M
WHERE M.OPR_ID = MyFile.Opr_ID
ORDER BY M.Acct_Nbr)
ORDER BY MyFile.OPR_ID, MyFile.ACCT_NBR DESC;


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks for your help. That was exactly what I needed. You are the greatest,
John!
 
Back
Top