Solved

Max Date and Group By SQL Server

Posted on 2014-10-16
2
87 Views
Last Modified: 2014-10-17
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
Comment
Question by:akscott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 40385950
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
 

Author Comment

by:akscott
ID: 40386737
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question