Q:

SQL using max function in triple join

I have 3 Table.  I want to join them all, I can do that.  But when I try to use MAX function, I got error.  
SELECT A.X, B.Y, SUBSTRING(str,CHARINDEX('C.Z',str)+12,4) as score,   MAX(A.Q)
FROM A(NOLOCK)
JOIN B
ON A.BID = B.id 
JOIN  C 
ON   B.Cid = C.ID
WHERE C.ApplicationDate BETWEEN '01-01-2013' AND GETDATE() 
group by B.Customer

If I don't write MAX function I get 
Col1, Col2, Col3, Col4
1 , 1  , 1 ,  1
1  ,    2 ,  3  ,  5
1  ,    3  , 2  ,   7
2 , 3, 3, 1
2, 3 , 1, 5
. , . , . , . 
. , . , . , . 
I want to get col4's max value for col1's 1 value.  For example 
I want to get 1,3,2,7 and 2,3,1,5 values in this example. 
How can I do that.  Thanks for your help. 
edit 
SELECT InstanceID, CustomerNo, Date, SUBSTRING(str,CHARINDEX('TTHA',str)+12,4) as score, MAX(SecondDate) as 'SearchDate'
FROM application(NOLOCK)
JOIN application_info 
ON application.application_info_id = application_info.id 
JOIN  Request 
ON   application.request_id = Request.id
WHERE Request. Date BETWEEN '01-01-2000' AND GETDATE() 
group by InstanceID,CustomerNo,Date,SUBSTRING(str,CHARINDEX('TTHA',str)+12,4)
I count not do your offers.  Could you help me on my code ? 
OUTPUT

Thanks

A:

replace your group by statement to this
group by A.X, B.Y, SUBSTRING(str,CHARINDEX('C.Z',str)+12,4)

