How to show who has the most billings?

[scoobyroo]

I have a table that list all the clients and matters with the billing attorneys and billings per attorney.
I need to show a list of clients and the billing attorney with the most billings. However, if there are two billing attorneys with the same billing total, I need to show both.

Here is some sample data:
Client Matter Atty Billing
SILICON IMAGE LICENSING 911 4,480.25
SILICON IMAGE LICENSING 1091 4,480.25
TRANSMETA CORP LICENSING 975 38,142.50
DENALI SOFTWARE GENERAL CORP 1403 3,070.00
DENALI SOFTWARE BOARD DIRECTORS 1403 444.50
DENALI SOFTWARE EMPLOYEE STOCK 1403 1,491.00
JAZIO INC GENERAL CORP 1206 1,425.50
JAZIO INC TRADEMARKS 1403 22.50

Silicon Image should show both attorneys since they have equal billings.
Transmeta should show atty 975.
Denali should show atty 1403.
Jazio should show atty 1206 (atty with the most billings).

ANy help would be appreciated. Thanks.

 

[bborissov]

SELECT Yourtable.*
FROM YourTable
INNER JOIN (SELECT Client, MAX(Billing) AS Billing 
                   FROM YourTable
            GROUP BY Client) Tbl1
      ON YourTable.Client  = Tbl1.Client AND
         YourTable.Billing = Tbl1.Billing

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[scoobyroo]

Thank you - this got me exactly what I needed.