Re: find the max

This was sent only to Art by mistake (sorry for the duplicate Art): Informix allows you to do weird stuff also... getting the max from customer's table columns zipcode and customer_num (it will always be the zipcode value): CREATE PROCEDURE get_max(i SET) RETURNING INTEGER;DEFINE mymax,curr_val INTEGER; LET mymax = NULL; FOREACH SELECT t.* INTO curr_val FROM TABLE(i) t IF (curr_val > mymax OR mymax IS NULL) THEN LET mymax=curr_val; END IF; END FOREACH RETURN mymax; END PROCEDURE; SELECT get_max(SET{zipcode,customer_num}), customer_num FROM customer; May need some testing... but looks geeky :) Ideally the SELECT inside the procedure would be a simple SELECT MAX() but I didn't figure out how to "name" the pseudo column... Regards. On Wed, Jan 4, 2012 at 9:02 PM, Art Kagel <art.kagel@gmail.com> wrote: > No certain what you are looking for. Is it that you want to know the > greatest value for each row of the five columns and what that value is? > > select keycols, > case when col1 >= col2 and col1 >= col3 and col1 >= col4 and> col1 >= col5 then col1 > when col2 >= col1 and col2 >= col3 and col2 >= col4 and > col2 >= col5 then col2 > when col3 >= col1 and col3 >= col2 and col3 >= col4 and > col3 >= col5 then col3 > when col4 >= col1 and col4 >= col2 and col4 >= col3 and > col4 >= col5 then col4 > when col5 >= col1 and col5 >= col2 and col5 >= col3 and > col5 >= col4 then col5 > end case, > othercols.... > > Or just write a small SPL or C function and pass in the five values and > return the greatest: > > create function greatest_of_five( int col1, int col2, int col3, int col4,> int col5 ) returning int; > if col1 >= col2 and col1 >= col3 and col1 >= col4 and col1 >= col5 > then return col1 > elif col2 >= col1 and col2 >= col3 and col2 >= col4 and col2 >= > col5 then return col2 > elif col3 >= col1 and col3 >= col2 and col3 >= col4 and col3 >= > col5 then return col3 > elif col4 >= col1 and col4 >= col2 and col4 >= col3 and col4 >= > col5 then return col4 > elif col5 >= col1 and col5 >= col2 and col5 >= col3 and col5 >= > col4 then return col5 > end if > end function; > > select keycols, greatest_of_five( col1, col2, col3, col4, col5 ),> othercols ... > ... > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > > > > On Wed, Jan 4, 2012 at 3:41 PM, Tom Lehr <tomcaml@gmail.com> wrote: > >> hi all >> >> trying to figure out how to go thru 5 separate columns and find the >> max value for each row from each field >> thru SQL. >> >> >> i don't see that informix has implemented the MAX scalar function as >> it is done in DB2 >> >> any suggestions? >> >> >> >> thanks >> tom >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >> > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...