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

x
Solved

# Creating an SQL view that pulls the oldest record per claimid

Posted on 2007-12-04
Medium Priority
909 Views
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.
0
Question by:boukaka
[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
• 2

LVL 21

Accepted Solution

mastoo earned 2000 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
0

LVL 75

Expert Comment

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
0

LVL 21

Expert Comment

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

Author Closing Comment

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

## Featured Post

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
###### Suggested Courses
Course of the Month4 days, 12 hours left to enroll