Max Date and Group By SQL Server
Posted on 2014-10-16
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.