Help with Query

I have the following table:
TableName: Customers
ID - Integer
CustomerID - VarChar
Name - VarChar
LoanNo= Int
Amount = Money
ID CustomerID Name LoanNo Amount
1 Chuck1 Chuck 1 2.00
2 Mike1 Mike 1 4.00
3 Dinah1 Dinah 1 6.00
4 James1 James 1 1.00
5 James1 James 2 3.00
6 Chuck1 Chuck 2 5.00
What I want to do is return all the Customers but only their highest LoanNo
(like MAX(LoanNo)).
Example:
ID CustomerID Name LoanNo Amount
2 Mike1 Mike 1 4.00
3 Dinah1 Dinah 1 6.00
5 James1 James 2 3.00
6 Chuck1 Chuck 2 5.00
Thanks,
Chuck> What I want to do is return all the Customers but only their highest
> LoanNo
> (like MAX(LoanNo)).
One method is with a derived table. Untested example:
SELECT
Customers.ID,
Customers.Name,
Customers.LoanNo,
Customers.Amount
FROM Customers
JOIN
(SELECT
CustomerID,
MAX(LoanNo) AS LoanNo
FROM dbo.Customers
GROUP BY CustomerID) AS MaxLoanNos ON
MaxLoanNos.CustomerID = Customers.CustomerID AND
MaxLoanNos.LoanNo = Customers.LoanNo
Hope this helps.
Dan Guzman
SQL Server MVP
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:eBja%23rl7GHA.3760@.TK2MSFTNGP02.phx.gbl...
>I have the following table:
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> Name - VarChar
> LoanNo= Int
> Amount = Money
> ID CustomerID Name LoanNo Amount
> 1 Chuck1 Chuck 1 2.00
> 2 Mike1 Mike 1 4.00
> 3 Dinah1 Dinah 1 6.00
> 4 James1 James 1 1.00
> 5 James1 James 2 3.00
> 6 Chuck1 Chuck 2 5.00
> What I want to do is return all the Customers but only their highest
> LoanNo
> (like MAX(LoanNo)).
> Example:
> ID CustomerID Name LoanNo Amount
> 2 Mike1 Mike 1 4.00
> 3 Dinah1 Dinah 1 6.00
> 5 James1 James 2 3.00
> 6 Chuck1 Chuck 2 5.00
> Thanks,
> Chuck
>