You actually don't need a subquery.  This should work:
SELECT   CustNo, 
	 MIN(Payment) AS [Low Payment],
	 MAX(Payment) AS [High Payment], 
----------------------> The Tricky Part <---------------------- 
       max( CASE isMostRecent When 1 then [Payment] else NULL End) as [Most
Recent Payment]
---------------------->

FROM	 phdBidHistory
GROUP BY CustNo

Brian Freeman
Carnegie Technology and Bluewave Computing
(xxx) xxx-xxxx  ext. 415
www.carnegie.com   / www.bluewave-computing.com


-----Original Message-----
From: David Garner [mailto:dgarner@g...]
Sent: Thursday, November 07, 2002 5:36 PM
To: sql language
Subject: [sql_language] Group By Complexities


I have a table named PaymentHistory with three columns (int), (money), 
(bit):

CustNo     Payment    isMostRecent
1          29.95      0
1          37.50      1
1          26.42      0
1          48.90      0
2          85.66      1
2          17.95      0
3          48.15      1
4          12.25      0
4          18.16      0
4          32.50      0

This table records the customer number(FK), the payments they have made to 
their account, and which payment was the most recent (if any).

I need to create a view that for each customer shows the lowest payment, 
the highest payment, and the most recent payment. The output of the view 
with the data from the above table should be:

[Cust No]     [Low Payment]   [High Payment]  [Most Recent Payment]
 1             26.42           48.90           37.50 
 2             17.95           85.66           85.66
 3             48.15           48.15           48.15
 4             12.25           32.50           NULL  

What I have come up with so far was the easy part:

SELECT   CustNo, 
	 MIN(Payment) AS [Low Payment],
	 MAX(Payment) AS [High Payment], 
----------------------> The Tricky Part <---------------------- 
         CASE 
	   WHEN MAX(CAST(isWinner as int)) = 1 
             THEN /* <<value of Payment where isMostRecent is true>> */
	   ELSE 
             NULL
	 END
	 AS [Most Recent Payment]
---------------------->

FROM	 phdBidHistory
GROUP BY CustNo

I guess what I'm asking is if it is possible to do a subquery on the rows 
in each group to get the necessary value.  If not, what would be a the 
best workaround.

David Garner