SQL Group By question

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, opportunityid
FROM         dbo.FilteredOpportunity
GROUP BY accountid, accountidname, new_producttype, opportunityid
HAVING      (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

Open in new window


I have 5 columns I'm pulling back from:

accountid; accountidname; [Latest Application Date]; new_producttype; opportunityid

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.
LVL 2
Steven O'NeillSolutions ArchitectAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
E.g.

 
WITH    Data
          AS ( 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 )
             )
    SELECT  O.*
    FROM    Ordered O
    WHERE   O.RN = 1;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
No points please.

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
;

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT    accountid, accountidname, new_applicationdate AS [Latest Application Date], new_producttype, opportunityid
FROM         dbo.FilteredOpportunity fo1
WHERE 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

Open in new window

0
Steven O'NeillSolutions ArchitectAuthor Commented:
Hi guys

Yeah thanx for that...forgot I'd asked the question some time ago as well lol (talk about total brain freexe today): http://www.experts-exchange.com/Database/MS-SQL-Server/SQL-Server-2005/Q_24685835.html

Thanx to you both :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.