Solved

Select MIN date from view

Posted on 2004-03-24
2
298 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
ID: 10671741
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
ID: 10671811
Thanks, Chris.

Had the join in the wrong place.

Much appreciated.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

777 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