Archives of the TeradataForum

Message Posted: Tue, 21 Jan 2003 @ 23:25:42 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Set solution, having to make my own test data:

drop table frank;
ct frank
 (c1 char(1)
, c2 date
, c3 dec(5,2));

ins frank values( 'a',date, -10.12);
ins frank values( 'a',date, -9.12);
ins frank values( 'a',date, -8.12);
ins frank values( 'a',date, -7.12); /* <-- should pick this one */
ins frank values( 'a',date-1, -10.12);
ins frank values( 'a',date-1, -0); /* <-- should pick this one */
ins frank values( 'a',date-1, -9.82);
ins frank values( 'a',date-1,  10.12);

/* routine */

ins blah_table
sel  f.*
from frank f
QUALIFY csum(1, c2 desc, (case when c3=0 then 1 /* zero numbers = 1 */
                               when c3<0 then 2 /* neg numbers = 2  */
                               when c3>0 then 3 /* pos numbers = 3  */
end   )  asc, abs(c3) asc ) = 1

group by c1,c2

where c3 <= 0 ;

--
Dwight Etheridge
Teradata Certified Master

	<Prev	Next>		<<First	<Prev	Next>	Last>>