Solved

Selecting data for the max date record in an aggregate query

Posted on 2015-01-27
4
111 Views
Last Modified: 2015-01-27
I am trying to write a query with following fields:

ID
Insurance
Amount
PayDate

The data has multiple records for each ID.  The Insurance is always SP.  I want to create a query that will return the
ID
Insurance
Max(Date) and the Amount associated with the Max(Date).

Thanks

Glen
0
Comment
Question by:GPSPOW
4 Comments
 
LVL 18

Accepted Solution

by:
SimonAdept earned 500 total points
ID: 40573934
SELECT ID, Insurance, Amount,PayDate from tbl T where paydate=(select max(paydate) from tbl where ID=T.ID)

NB you may get more than one line for a given ID if there was more than one payment on the same max(Paydate).
0
 

Author Closing Comment

by:GPSPOW
ID: 40573957
Thanks

Worked Great!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40573967
Or, using a subquery..
SELECT ID, Insurance, yt_max.max_date, yt.PayDate
FROM YourTable yt
JOIN (
   SELECT ID, Insurance, Max(Date) as max_date
   FROM YourTable
   GROUP BY id, Insurance) yt_max ON yt.ID = yt_max.ID AND yt.date = yt_max.max_date

Open in new window


I have an article called SQL Server GROUP BY Solutions, scroll down to '5. Aggregate AND values from a single row that make up the aggregate: Subquery'
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40574629
Only because it wasn't mentioned...

You can use row_number() for this
SELECT ID, Insurance, Amount,PayDate
FROM  (
      SELECT ID, Insurance, Amount,PayDate, row_number() over(partition by ID order by PayDate DESC) as rn
      FROM YourTable
      ) as derived
WHERE rn = 1

Open in new window


Here you can only get one row per ID even if there happens to be more than 1 payment on a paydate
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now