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
> 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
> 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
> 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>)
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