Selecting data for the max date record in an aggregate query

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


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


Question by:GPSPOW
LVL 18

Accepted Solution

Simon 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).

Author Closing Comment

ID: 40573957

Worked Great!
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
   SELECT ID, Insurance, Max(Date) as max_date
   FROM YourTable
   GROUP BY id, Insurance) yt_max ON yt.ID = yt_max.ID AND = 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'
LVL 48

Expert Comment

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

You can use row_number() for this
SELECT ID, Insurance, Amount,PayDate
      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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL tables, .net application and ETL connundrum 3 53
sql 2014,  lock limit 5 29
Query group by data in SQL Server - cursor? 3 29
SQL view 2 26
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 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

17 Experts available now in Live!

Get 1:1 Help Now