We help IT Professionals succeed at work.
Get Started

SQL Group By question

Last Modified: 2015-02-26
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.
Watch Question
Senior Developer
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE