Ben:
It looks to me like your solution will not work. The condition below eliminats ID2
from consideration:
and (t2.A+t2.B) < (t1.A+t1.B) and t1.ID = (.zID)
The term (A + B) is 13, 15, and 15 on the three records.
What looks to me to be a solution is to recognize that knowing the highest A that is
less than the A in ID3 is all that is needed:
Assuming structure: Table (ID text, A int, B int)
set var vID text = 'ID3'
select ID, A, B from Table WHERE A = (select max(A) from Table WHERE A < (select A
from Table WHERE ID = .vID))
Jim Blackburn
Kodiak
Ben Petersen wrote:
>
> On 27 Dec 2001, at 15:21, Gary Winzeler wrote:
> > Whats the two steps?
> 1)
> Assuming "Table" looks like this
> ID, A, B
> ------------
> ID1,1,12
> ID2,4,11
> ID3,7,8
>
> Set v vID Text = 'ID1'
>
> Create temp view Test (ID, A, B, AB) as +
> sel t2.ID, t2.A, t2.B, (t2.A+t2.B) +
> from Table t1, Table t2 +
> group by t2.ID, t2.A, t2.B +
> whe t1.A between t2.A and t2.B and (t2.A+t2.B) < (t1.A+t1.B) and
> t1.ID = (.zID)
>
> This is probably a bit much just for the three records in my
> example, but in reality there could be many groupings like this in
> various sizes. This view extracts all records two numerics would
> enclose record being queried.
>
> 2)
> sel ID from Test whe AB = (sel max(AB) from Test)
> ----------------------------
> >From the few samples I've tested, this logic holds up, but I'm not
> certain yet. This just seems a bit convoluted, and was looking for a
> cleaner solution.
>
> Thanks,
>
> Ben
>
> >
> > At 03:07 PM 12/27/2001 +0000, you wrote:
> >
> > >Hi all. Looking at the snippit of data records below, how would a
> > >person do a select that, using ID3 data, would return ID2 (and not
> > >ID1)?
> > >
> > >Working from the bottom up, you can count on the two numerics of
> > >each record being bounded by the next record, ie 4 and 11 of ID2
> > >would enclose 7 and 8 of ID3... 1 and 12 of ID1 enclose 4 and 11 of
> > >ID2.
> > >
> > >So, if you only knew the values of ID3, how would you construct a
> > >select that only returned ID2? I can do it in two steps (I think), but
> > >would rather one.
> > >
> > >ID1,1,12
> > >ID2,4,11
> > >ID3,7,8
> > >
> > >tia,
> > >
> > >Ben Petersen
> > >================================================
> > >TO SEE MESSAGE POSTING GUIDELINES:
> > >Send a plain text email to [EMAIL PROTECTED]
> > >In the message body, put just two words: INTRO rbase-l
> > >================================================
> > >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > >In the message body, put just two words: UNSUBSCRIBE rbase-l
> >
> > ================================================
> > TO SEE MESSAGE POSTING GUIDELINES:
> > Send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: INTRO rbase-l
> > ================================================
> > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: UNSUBSCRIBE rbase-l
> >
>
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: INTRO rbase-l
> ================================================
> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
