MAX only returning 1 row

[jmuslin]

I would really like some help on this, I'm befuddled. In the table, mwlCustomField, a LoanApp_ID could have multiple rows. What I am trying to do is loop through the rows, find the ones with the same LoanApp_ID, determine which record was last entered by comparing the DateValue, and only return the record that was last entered. This stored procedure only returns one record that has the lastest DateValue. I need this for each record....

Clear as mud?

SELECT mwlLoanApp.AppNum,
mwlLoanApp.OriginatorName,
mwlLoanApp.ProcessorName,
mwlLoanApp.LoanNumber,
mwlBorrower.Fullname,
mwlCustomField.DateValue
FROM mwlLoanApp INNER JOIN
mwlBorrower ON mwlLoanApp.ID = mwlBorrower.LoanAPP_ID INNER JOIN
mwlCustomField ON mwlLoanApp.ID = mwlCustomField.LoanApp_ID
WHERE (mwlCustomField.DateValue =
(SELECT MAX(DateValue) AS Expr1
FROM mwlCustomField AS mwlCustomField_1
WHERE (CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48') OR
(CustFieldDef_ID = 'CDCAE4277D10465F8960853976EAE6B6') OR
(CustFieldDef_ID = 'C6B0C4C8B67D4B9589B91104BE7FCF54') OR
(CustFieldDef_ID = '781DED61758C4001B24D56F971E1B4E4')))

Any help would be greatly appreciated.

Thank you and have a fantastical day

[jmuslin]

It seems my stored procedure got broken up, here it is again:
SELECT mwlLoanApp.AppNum, mwlLoanApp.OriginatorName, mwlLoanApp.ProcessorName, mwlLoanApp.LoanNumber, mwlBorrower.Fullname,
mwlCustomField.DateValue
FROM mwlLoanApp INNER JOIN
mwlBorrower ON mwlLoanApp.ID = mwlBorrower.LoanAPP_ID INNER JOIN
mwlCustomField ON mwlLoanApp.ID = mwlCustomField.LoanApp_ID
WHERE (mwlCustomField.DateValue =
(SELECT MAX(DateValue) AS Expr1
FROM mwlCustomField AS mwlCustomField_1
WHERE (CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48') OR
(CustFieldDef_ID = 'CDCAE4277D10465F8960853976EAE6B6') OR
(CustFieldDef_ID = 'C6B0C4C8B67D4B9589B91104BE7FCF54') OR
(CustFieldDef_ID = '781DED61758C4001B24D56F971E1B4E4')))

[gbritton]

Something like this:

SELECT mwlLoanApp.AppNum
	,mwlLoanApp.OriginatorName
	,mwlLoanApp.ProcessorName
	,mwlLoanApp.LoanNumber
	,mwlBorrower.Fullname
	,q.DateValue
FROM mwlLoanApp
INNER JOIN mwlBorrower
	ON mwlLoanApp.ID = mwlBorrower.LoanAPP_ID
INNER JOIN (
	SELECT MAX(DateValue) AS Expr1
		,LoanApp_ID
	FROM mwlCustomField AS mwlCustomField_1
	WHERE (CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48')
		OR (CustFieldDef_ID = 'CDCAE4277D10465F8960853976EAE6B6')
		OR (CustFieldDef_ID = 'C6B0C4C8B67D4B9589B91104BE7FCF54')
		OR (CustFieldDef_ID = '781DED61758C4001B24D56F971E1B4E4')
	GROUP BY LoanApp_ID
	) q
	ON mwlLoanApp.ID = q.LoanApp_ID

[jmuslin]

SELECT mwlLoanApp.AppNum
,mwlLoanApp.OriginatorName
,mwlLoanApp.ProcessorName
,mwlLoanApp.LoanNumber
,mwlBorrower.Fullname
,q.DateValue
FROM mwlLoanApp
INNER JOIN mwlBorrower
ON mwlLoanApp.ID = mwlBorrower.LoanAPP_ID
INNER JOIN (
SELECT MAX(DateValue) AS Expr1
,LoanApp_ID
FROM mwlCustomField AS mwlCustomField_1
WHERE (CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48')
OR (CustFieldDef_ID = 'CDCAE4277D10465F8960853976EAE6B6')
OR (CustFieldDef_ID = 'C6B0C4C8B67D4B9589B91104BE7FCF54')
OR (CustFieldDef_ID = '781DED61758C4001B24D56F971E1B4E4')
GROUP BY LoanApp_ID
) q
ON mwlLoanApp.ID = q.LoanApp_ID

Awesome sauce! Except I am getting an error on Line 6, Invalid column name.
Line 6 = q.DateValue

I tried replacing q with mwlCustomField and mwlCustomFiel_1 with no luck. Any ideas?

thanks again!

[gbritton]

Sorry, You aliased the date as Expr1 so replace q.DateValue with q.Expr1

[jmuslin]

ah HA! You're awesome!

thank you thank you thank you

[gbritton]

glad to help!!!

[jmuslin]

If I wanted to grab additional data from the Select(Max(DateValue)...) statement but it is in the same column, DateValue, but with a different row ID, how would I go about doing that?

[jmuslin]

I was thinking that since the Select(Max(DateValue)...) is returning the rows I want, I would be able to piggyback off of that??

[gbritton]

not sure what you mean. some examples would help

[jmuslin]

Ok, in the first Select(Max(DateValue)...) statement, you helped me grab the latest EarlyLockExpire date. Now, I need to grab the latest EarlyLock date. The data is in the same table, uses the same column name, DateValue, but different CustFieldDef_IDs. I need the latest DateValue for both the EarlyLockExpire date and the EarlyLock date.

table design looks like:
ID LoanApp_ID CustFieldDef_ID DateValue

As you can tell, my SQL knowledge is pretty limited.

Thank you

[gbritton]

As a first cut, I would add another join similar to the last one, with the same join condition, but this time pulling out the other date.

Another way is to use a cross-tab approach. Here's a simple example:

declare @ table (a int, b int)
insert into @ (a,b) values (1,2),(3,4),(5,6)
select a
     , max(case when a = 1 then b end) as  max1
	 , max(case when a = 2 then b end) as  max2
	 , max(case when a = 3 then b end) as  max3
from @
group by a

[jmuslin]

Like so?
SELECT mwlLoanApp.AppNum,
mwlLoanApp.OriginatorName,
mwlLoanApp.ProcessorName,
mwlLoanApp.LoanNumber,
mwlBorrower.Fullname,
q.EarlyLockExpire,
p.EarlyLock
FROM mwlLoanApp INNER JOIN
mwlBorrower ON mwlLoanApp.ID = mwlBorrower.LoanAPP_ID INNER JOIN
(SELECT MAX(DateValue) AS EarlyLockExpire, LoanApp_ID
FROM mwlCustomField AS mwlCustomField_1
WHERE (CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48') OR
(CustFieldDef_ID = 'CDCAE4277D10465F8960853976EAE6B6') OR
(CustFieldDef_ID = 'C6B0C4C8B67D4B9589B91104BE7FCF54') OR
(CustFieldDef_ID = '781DED61758C4001B24D56F971E1B4E4')
GROUP BY LoanApp_ID) AS q ON mwlLoanApp.ID = q.LoanApp_ID
INNER JOIN mwlLoanData ON mwlLoanApp.ID = mwlLoanData.ObjOwner_ID
INNER JOIN
(SELECT MAX(DateValue) AS EarlyLock, LoanApp_ID
FROM mwlCustomField AS mwlCustomField_1
WHERE (CustFieldDef_ID = '123') OR
(CustFieldDef_ID = '456') OR
(CustFieldDef_ID = '789') OR
(CustFieldDef_ID = '012')
GROUP BY LoanApp_ID) AS p ON mwlLoanApp.ID = p.LoanApp_ID