Return Record with highest value 1

[sjck]

Please Help. I need to write a query that will return the record FS number that is used the most often for the TIN. So for example I have the following table

tblOAFeeScheduleListing
T30 Tin Nbr T46 Fs Nbr CountOfT46 Fs Nbr myID
328328038 9053 1 15
382617193 1201 1 16
382617193 2000 79 17
382617193 207 78 18
382617193 2550 178 19
382617193 265 88 20

And I need the following result

tblOAFeeScheduleListing
T30 Tin Nbr T46 Fs Nbr CountOfT46 Fs Nbr myID
328328038 9053 1 15
382617193 2550 178 19

I have tried using the max function, but just get the results of the top table. I have also tried modeling my query after ones I have found in the forum using and only receive one record back. Below is my SQL

SELECT tblOAFeeScheduleListing.[T30 Tin Nbr], tblOAFeeScheduleListing.[T46 Fs Nbr], tblOAFeeScheduleListing.[CountOfT46 Fs Nbr]
FROM tblOAFeeScheduleListing INNER JOIN tblOAFeeScheduleListing AS tblOAFeeScheduleListing_1 ON tblOAFeeScheduleListing.myID = tblOAFeeScheduleListing_1.myID
where tblOAFeeScheduleListing.[CountOfT46 Fs Nbr] = (SELECT MAX([CountofT46 Fs Nbr]) FROM [tblOAFeeScheduleListing]);

Any help is appreciated….

 

[PHV]

Perhaps the TOP predicate ?
SELECT TOP 2 [T30 Tin Nbr], [T46 Fs Nbr], [CountOfT46 Fs Nbr], myID
FROM tblOAFeeScheduleListing
ORDER BY 2 DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sjck]

Thanks for you quick response....

I tried the above query and the result I get is all the records that have the most (highest) FS number.

The result I need is for each TIN, to return the FS that has the most providers using it. I need 108 - one for each Unique TIN that shows the FS number..

 

[PHV]

Sorry, I've misunderstood the requirment :~/
And what about something like this ?
SELECT A.[T30 Tin Nbr], A.[T46 Fs Nbr], A.[CountOfT46 Fs Nbr], A.myID
FROM tblOAFeeScheduleListing As A INNER JOIN (
SELECT [T30 Tin Nbr], Max([CountOfT46 Fs Nbr]) As Most
FROM tblOAFeeScheduleListing GROUP BY [T30 Tin Nbr]
) As M ON A.[T30 Tin Nbr] = M.[T30 Tin Nbr] AND A.[CountOfT46 Fs Nbr] = M.Most

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sjck]

Thank you for your quick respones Again... It worked...Thank you agian for your help....