I have a SQL statement that I'm looking to be able to perform a MAX upon a date field. The code I currently have is below and it will produce a list of everything I need but I only need to see the last update if all the other fields match up.

Basically I think I need to perform a MAX on the dbo.PS_Business_Base.Created field but if I add a Group By clause to the query then it breaks so I'm assuming I need to take the Grouping out a level to allow the grouping to take place first and then perform the usual SELECT.

Any ideas would be appreciate as always guys.

SELECT     dbo.PS_Business_Base.Name AS [Business Name], dbo.PS_Business_Base.Region, dbo.PS_Application_Base.ProductType AS [Type of Funding], 
                      dbo.PS_Application_Base.LoanAmountApproved AS [Loan Amount], dbo.PS_Application_Base.GrantAmountApproved AS [Grant Amount], 
                      dbo.PS_Application_Base.PeriodOfLoan AS [Period of Loan], 
                      dbo.PS_Application_Base.DeferredInitialRepaymentPeriod AS [Deferred Initial Repayment Period], 
                      dbo.PS_Application_Base.InterestOnlyPeriod AS [Interest Only Period], dbo.PS_Application_Base.PanelDate AS [Panel Date], 
                      dbo.PS_Application_Base.ApprovedDate AS [Approved Date], dbo.PS_Control.ValidationMessages AS [Validation Messages], 
                      dbo.PS_Control.ProposalCreationMessages AS [Proposal Creation Messages], dbo.PS_Control.Completed AS [Process Date], 
                      dbo.PS_Control.Reference, dbo.PS_Application_Base.ApplicationGUID, dbo.PS_Control.UpdateType, dbo.PS_Control.ProcessFlag, 
                      dbo.PS_Business_Base.Created AS [Date Created]
FROM         dbo.PS_Application_Base INNER JOIN
                      dbo.PS_Business_Application_Link INNER JOIN
                      dbo.PS_Business_Base ON dbo.PS_Business_Application_Link.BusinessGUID = dbo.PS_Business_Base.BusinessGUID ON 
                      dbo.PS_Application_Base.ApplicationGUID = dbo.PS_Business_Application_Link.ApplicationGUID INNER JOIN
                      dbo.PS_Control ON dbo.PS_Application_Base.ApplicationGUID = dbo.PS_Control.Reference
WHERE     (dbo.PS_Control.UpdateType = 0) AND (dbo.PS_Control.ProcessFlag <> 0) AND (dbo.PS_Control.ProcessFlag <> 10)

Select allOpen in new window