Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Selecting data for the max date record in an aggregate query

Posted on 2015-01-27
4
Medium Priority
?
130 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
[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
4 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 2000 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 66

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 49

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

721 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