Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Grouping SQL Server Query Question

Posted on 2011-09-26
3
Medium Priority
?
241 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 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

610 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