| Dan Guzman 2006-10-24, 6:37 pm |
| > 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@CreateItSoft
ware.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
>
>
|