Solved

Grouping SQL Server Query Question

Posted on 2011-09-26
3
220 Views
Last Modified: 2012-05-12
Hello experts,

I have this table structure

Application
ID, ContactID, ProgramTypeId, ReceivedDate, OtherMetadata

I want to get only the most recent ReceivedDate for a given ContactID, ProgramTypeId combination.

Let me know if you would like me to add sample data.

Thanks!
0
Comment
Question by:freezegravity
[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
3 Comments
 
LVL 15

Expert Comment

by:tim_cs
ID: 36599636
;WITH CTE AS (
SELECT
   ContactID
   ,ProgramTypeID
   ,ReceivedDate
   ,ROW_NUMBER() OVER (Partition By ContactID, ProgramTypeID ORDER BY ReceivedDate DESC) RN
FROM
   Application
)

SELECT
   *
FROM
   CTE
WHERE
   RN = 1
0
 
LVL 5

Accepted Solution

by:
bitref earned 500 total points
ID: 36599687
Select ContactID, ProgramTypeId, MAX(ReceivedDate)
From Application
Group By ContactID, ProgramTypeId

Open in new window


0
 

Author Closing Comment

by:freezegravity
ID: 36600198
I ended up using this query as it was easier to understand and gave the results I was expecting.

Thanks!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

733 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