Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • Last Modified:

Max Date and Group By SQL Server

I'm working on a CRM report.  I want to show the last contact history per account.  The history is all housed in one table, account information in the other.

Pertinint fields in History are:

HistoryID, Account, Date
Where there can be many dates and history IDs related to the account

I'm trying to find the latest date by doing a MAX expression on the date field and grouping by the Account field -- that works fine, but I need to return the HistoryID related to that record in order to join it to the account table.

I hope this makes enough sense for you to help.

For now, I've used a query joining the account to history with all history and then filtered within SSRS to get the results for this one report, but I'd like to create a view of just the HistoryID, Account, Date from the History table for future use.
0
akscott
Asked:
akscott
1 Solution
 
chaauCommented:
These type of queries are best performed with a help of a window function:
with a as (
Select Account, HistoryID, Date, ROW_NUMBER() OVER (PARTITION BY Account ORDER BY Date DESC) as rn
From History )
SELECT Account, HistoryID, Date FROM a WHERE rn = 1

Open in new window

0
 
akscottAuthor Commented:
I don't understand it, but it worked. There's a section on partitions in my SQL book -- looks like I need to read that.  Thank you!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now