Creating an SQL view that pulls the oldest record per claimid

Posted on 2007-12-04
Last Modified: 2012-06-22
I'm having trouble creating a view that will pull the oldest record on each file in a table so that I can report on it.
A sample of what the table looks like is below. What I need to do is capture the reserve amount of the oldest historydate for each claimid.

claimid    historydate    reserveamount
111222  Aug 1 2004    40.00
111222  Aug 2 2005    4000.00
111222  Sep 1 2007     365.40
333333  Jan 4 2006     1000.00
333333  Jan 6 2006      1040.00
999999  Nov 1 2007     5000.00
250000  Jan 20 2007    10.00
250000  Jan 21 2007     20.00
250000  Jan 22 2007     0.00

The result from the view on the above sample table should return this:
claimid    historydate    reserveamount
111222  Aug 1 2004    40.00
333333  Jan 4 2006     1000.00
999999  Nov 1 2007     5000.00
250000  Jan 20 2007    10.00

Can anyone help me write this? I tried top(1)* but that just pulls the top one from the entire table not the top one per claimid.
Question by:boukaka
  • 2
LVL 21

Accepted Solution

mastoo earned 500 total points
ID: 20405002
Select mt.claimid, mt.historydate, mt.reserveamount
  From mytable As mt Join ( Select claimid, max( historydate ) As MaxHistory
    From mytable ) As T2
    On mt.claimid = T2.claimid And mt.historydate = T2.MaxHistory
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20405053
Just a minor correction.  No points please:

Select mt.claimid, mt.historydate, mt.reserveamount
From mytable As mt
      Join (
            Select claimid, max( historydate ) As MaxHistory
            From mytable
            Group By claimid) As T2 On mt.claimid = T2.claimid And mt.historydate = T2.MaxHistory
LVL 21

Expert Comment

ID: 20405116
Thanks.  Once I go beyond 2 lines of code there's bound to be an error :-)

Author Closing Comment

ID: 31412641
Oh wow, you're a lifesaver! thank you SO much!!

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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…

830 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