Re: remapping and keeping original value?

From: Douglas Nichols <dnichols_at_fhcrc.org>
Date: Fri, 19 Nov 1999 05:44:04 +0000
Message-ID: <3834E3A4.CD59563D@fhcrc.org>

Thanks, I am getting closeunfortunately your gives me two values Originally what I wrote was this but do not like the ugliness of the nestedd loops.

 select nwtsid, inflam4 as inflam
   from nwts5.histol
  where (nwtsid, DECODE(inflam4,1,1,0,2,2,3,3,4,5))

	 IN (select distinct nwtsid, min(DECODE(inflam4,1,1,0,2,2,3,3,4,5))
 		from nwts5.histol
 		where specim4 <=6
 		group by nwtsid)

I think I'll just mull it over a bit. You little note got me thinking about other ways though and I really appreciate it.

dn
Steve McDaniels wrote:
>
> When I have trouble, I try to break the problem into smaller pieces.
>
> Here's my thinking:
>
> select id, var2, decode(var2,.........................) flag from a_table;
>
> Then if this appears to be producing the basic data you want,
>
> select id, var2, min(flag) flag
> from
> ( select id, var2, decode(var2,.........................) flag from
> a_table )
> group by id, var2
>
> Then if this appears to be producing the results you want,
> try and get rid of the nested query.
>
> Douglas Nichols <dnichols_at_fhcrc.org> wrote in message
> news:38349285.20847BAA_at_fhcrc.org...
> > I am trying to remap a field so I can order the out come then get the
> > original field.
> > Something like this:
> > I have mydata ( id, var1, var2);
> > I need to eval var2 as decode(var2, 1,1,0,2,2,3,4,0,5) as flag;
> > Find the lowest value of flag then return id, var2
> >
> > something like
> > select id, var2, min(decode(var2,1,1,0,2,2,3,3,0,5)) as flag
> > from mydata
> > group by id;
> >
> > This doesn't but this is like what I am after.
> >
> > Thanks ahead of time!
> >
> > --
> > Cheers, dn
> >
> > Douglas Nichols dnichols_at_fhcrc.org
> > ---------------------------------------------------------------
> > National Wilms Tumor Study Group 206.667.4283
> > Seattle, WA

--
Cheers, dn

Douglas Nichols                              dnichols_at_fhcrc.org
---------------------------------------------------------------
National Wilms Tumor Study Group                   206.667.4283

Seattle, WA Received on Thu Nov 18 1999 - 23:44:04 CST