[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 135
  • Last Modified:

Selecting data for the max date record in an aggregate query

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
GPSPOW
Asked:
GPSPOW
1 Solution
 
SimonCommented:
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
 
GPSPOWAuthor Commented:
Thanks

Worked Great!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
PortletPaulCommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now