Re: find the max

Over complicated things: select ( select MAX(t1.col1) from table( set{t0.zipcode::integer, t0.customer_num::integer} ) t1(col1) ) max_zip_cust_num, zipcode, customer_num from customer t0; max_zip_cust_num zipcode customer_num 94086 94086 101 94117 94117 102 94303 94303 103 94026 94026 104 94022 94022 105 94063 94063 106 94304 94304 107 94063 94063 108 94086 94086 109 94062 94062 110 94085 94085 111 94022 94022 112 94025 94025 113 94062 94062 114 94025 94025 115 94040 94040 116 94063 94063 117 94609 94609 118 8002 08002 119 85016 85016 120 19898 19898 121 8540 08540 122 32256 32256 123 74006 74006 124 2135 02135 125 80219 80219 126 60406 60406 127 85008 85008 128 The casts assure that we're creating a set of integers... So I think you can easily adapt this to your query? Regards. On Thu, Jan 5, 2012 at 11:44 PM, Fernando Nunes <domusonline@gmail.com>wrote: > 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... > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...