Group By Last Date

[ronspree]

I have a table named Bills (BillID is the Primary Key):

BillID   CompanyID    Date     Amount
--       --------   ------    -------
1          10001    01/01/06   100.00
2          10001    02/20/06   200.00
3          10002    01/05/06  5993.21
4          10002    02/22/06   245.20
5          10002    03/24/06   336.28
6          10003    01/22/06   123.45

and want an output that selects the last date and its corresponding amount grouped by company id:

CompanyID   LastDate     Amount
----------  ----------   -------
10001       02/20/06      200.00
10002       03/24/06      336.28
10003       01/22/06      123.45

I started with

SELECT CompanyID, MAX(Date) as LastDate FROM Bills GROUP BY CompanyID

which selects the correct "LastDate" of each CompanyID. However, I can't seem to include the Amount column. How do I make my SELECT statement include the Amount column?

 

[bborissov]

SELECT Bills.CompanyID, Bills.Date as LastDate, Bills.Amount
       FROM Bills
INNER JOIN (SELECT CompanyID, MAX(Date) as LastDate
                   FROM Bills
                   GROUP BY CompanyID) Tbl1
ON Bills.CompanyID = Tbl1.CompanyID AND
   Bills.Date      = Tbl1.Date

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

 

[ronspree]

borislav, thank you so much for your reply. Do you realize that you answered is just 4 minutes? Haha! I'm very impressed!

This is the first time I've seen a join involving two fields. I didn't know it could be done. I never needed to use it until now.

Thanks again.