I have a simply query where I'm trying to pull the most recent record from my table grouped by a certain field but I'm having a bit of brain freeze today so hopefully someone will be able to show me where I'm going wrong:
There will be times where the accountid will have multiple opportunityid records but I only want the most recent one (the new_applicationdate).
Here is the code I have atm:
SELECT TOP (100) PERCENT accountid, accountidname, MAX(new_applicationdate) AS [Latest Application Date], new_producttype, opportunityidFROM dbo.FilteredOpportunityGROUP BY accountid, accountidname, new_producttype, opportunityidHAVING (MAX(new_applicationdate) IS NOT NULL) AND (new_producttype IN (1, 2, 3, 4, 5)) AND (accountid = '40B98E4E-FAE1-E111-A6E2-00155D101911')ORDER BY accountidname
What is happening right now is I am getting all the opportunityid records coming back for the accountid and I'm only after the most recent one.
I'm aware I could change the code to SELECT TOP(1)... but this will not work as I'm merely filtering on the accountid for testing and this will be removed so it will be looking at a table with thousands of rows in it.
Hope that makes sense and any advice appreciated as always.
I agree with ste5an row_number() is great for this requirement, but it does not have to be via a CTE
, a conventional derived table does it too.
(sorry ste5an, I'm just seeing a flurry of CTE approaches for nigh on everything)
SELECT O.*FROM ( SELECT FO.accountid , FO.accountidname , FO.new_applicationdate , FO.new_producttype , FO.opportunityid , ROW_NUMBER() OVER ( PARTITION BY FO.accountid ORDER BY FO.new_applicationdate DESC ) AS RN FROM dbo.FilteredOpportunity FO WHERE FO.new_applicationdate IS NOT NULL AND FO.new_producttype IN ( 1, 2, 3, 4, 5 ) ) AS O WHERE O.RN = 1;
SELECT accountid, accountidname, new_applicationdate AS [Latest Application Date], new_producttype, opportunityidFROM dbo.FilteredOpportunity fo1WHERE new_producttype IN (1, 2, 3, 4, 5) AND (accountid = '40B98E4E-FAE1-E111-A6E2-00155D101911') AND new_applicationdate = (SELECT MAX(fo2.new_applicationdate) FROM dbo.FilteredOpportunity fo2 WHERE fo1.accountid = fo2.accountid AND fo1.new_producttype=fo2.new_producttype)ORDER BY accountidname
I agree with ste5an row_number() is great for this requirement, but it does not have to be via a CTE
, a conventional derived table does it too.
(sorry ste5an, I'm just seeing a flurry of CTE approaches for nigh on everything)
Open in new window