LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (September 2001, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


Back to: Top of message | Previous page | Main SAS-L page