Solved

Select MIN date from view

Posted on 2004-03-24
2
277 Views
Last Modified: 2008-02-26
I have a view (vwHistory).  This view is a history of events that have happened to a claim.  It looks something like this:

vwHistory
ClaimNumber (varchar)
EventDate (DateTime)
Attribute1 (varchar)
Attribute2 (varchar)
Attribute3 (varchar)

ClaimNumber and EventDate together make a composite key.

I need a select statement that will give me all of the data values for the earliest EventDate for a particular claim number.

For example if my table looks like this:

ClaimNumber   EventDate     Att1        Att2      Att3
1                     1/1/04          A            B          C
1                     1/2/04          D            E          F
1                     1/3/04          G            H          I
2                     1/1/04          J             K          L
2                     1/2/04          M           N          O
3                     1/5/04          P            Q          R

Then my result set would look like this:

ClaimNumber   EventDate     Att1        Att2      Att3
1                     1/1/04          A            B          C
2                     1/1/04          J             K          L
3                     1/5/04          P            Q          R


Attributes 1 - 3 may or may not be identical for each row.


Thanks in advance,
HawkeyeNash
0
Comment
Question by:HawkeyeNash
2 Comments
 
LVL 7

Accepted Solution

by:
ChrisFretwell earned 125 total points
Comment Utility
Here's one way

select vw.claimnumber, vw.eventdate, vw.att1, vw.att2,vw.att3
from vwhistory vw join
(select claimnumber, min(eventdate) as mindate from vwhistory group by claimnumber) as v2 on vw.claimnumber = v2.claimnumber and vw.eventdate = v2.mindate
order by vw.claimnumber
0
 

Author Comment

by:HawkeyeNash
Comment Utility
Thanks, Chris.

Had the join in the wrong place.

Much appreciated.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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…

744 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

18 Experts available now in Live!

Get 1:1 Help Now