Date:         Tue, 18 Sep 2001 11:02:17 -0400
Reply-To:     Richard DeVenezia <radevenz@IX.NETCOM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Richard DeVenezia <radevenz@IX.NETCOM.COM>
Organization: MindSpring Enterprises
Subject:      Re: SQL problem- need help

"Conrad, Douglas" <Douglas.Conrad@FMR.COM> wrote in message
news:E58B27C25349D51198C90008C7918F008E564E@msgmro576nts.fmr.com...
> Hello SQL Mavens,
> I am trying to do this in 1 step and am not sure if this can be done.
> Basically, I'm grabbing one variable from the join of two tables with many
> conditions
> and this variable then populates a new variable in another table.
>
> Currently the value that is taken is the min(ID) but that is wrong. It
needs
> to be the first ID based on the EARLIEST DATE of another field. (basically
> the first person who enters the info needs to be the critical ID and is
> tracked in the other table.)
>

Since you are using two independent ordering criteria in your scheme to
select a row, you need to nest one in the other.
Additionally, if you can not be assured that each row has a unique ID, you
will probably have to impose additional criteria, or create a view that
guarantees a unique pseudo-id that can be used in an additional criteria.

If ID is unique,

select value from
(select id, value
 from (select <joining ONE and TWO>)
 having ONE.date=min(ONE.date)
)
where id=MIN(id)


This query may not translate to other SQL systems.
The id=MIN(id) kind of clause is a SQL extension that SAS calls remerging
(see the online help).  [ In other SQL's you would need a very similar query
to obtain the MIN value that would then be used for matching. ]


--
Richard DeVenezia - SAS Macros and AF Tools
http://www.devenezia.com