Solved

Select MIN date from view

Posted on 2004-03-24
2
315 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
[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
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
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…

739 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