find the max

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

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 >