# Re: Adding values returned by GREATEST

*Date*: Sat, 14 May 2016 12:16:11 -0400*From*: "shawn l.green" <shawn.l.green@xxxxxxxxxx>*Subject*: Re: Adding values returned by GREATEST

Hello Sukhjinder, On 5/3/2016 8:55 AM, Sukhjinder K. Narula wrote:

Hello, i have a question regarding the GREATEST function of mysql. I would like to add the values returned by GREATEST function is mysql, so a query is like below: For example table t has 6 fields with values as follows: A = 1, B = 3, C=0, D = 0, E = 1 and F = 0 and I run a query: SELECT GREATEST (A, B, C) AS G1, GREATEST (D, E, F) AS G2, ( GREATEST (A, B, C) + GREATEST(D, E, F) ) AS Total FROM t The result row I expect is: 3, 1, 4 But I get 3, 1, 6 However when I run the query like below I get correct results as total being 4: SELECT ( GREATEST (1, 3, 0) + GREATEST(0,1,0) ) AS Total So what I noticed is as I add result from GREATEST function, the result is adding 1 for each GREATEST call I have in total. So, if I change my query as below: SELECT GREATEST (A, B, C) AS G1, GREATEST (D, E, F) AS G2, ( GREATEST (A, B, C) + GREATEST(D, E, F) + GREATEST(D, E, F) ) AS Total FROM t The results will be 3, 1, 8 GREATEST (A, B, C) + GREATEST(D, E, F) + GREATEST(D, E, F) is calculated as GREATEST (A, B, C) = 3 + 1 GREATEST(D, E, F) = 1 +1 GREATEST(D, E, F) = 1 +1 So the total is 8. I have tried online to search for this type of behaviour but no luck. Can anyone please explain this. Many Thanks, SK

I attempted to reproduce this problem but was unable to do so. (testing with 5.7.11) localhost.test>SELECT @@version; +---------------------------------------+ | @@version | +---------------------------------------+ | 5.7.11-enterprise-commercial-advanced | +---------------------------------------+

`localhost.(none)>select greatest(1,3,0), greatest(0,1,0),`

`greatest(1,3,0)+ greatest(0,1,0) as total`

-> ; +-----------------+-----------------+-------+ | greatest(1,3,0) | greatest(0,1,0) | total | +-----------------+-----------------+-------+ | 3 | 1 | 4 | +-----------------+-----------------+-------+ 1 row in set (0.00 sec) localhost.(none)>set @A = 1, @B = 3, @C=0, @D = 0, @E = 1, @F = 0; Query OK, 0 rows affected (0.00 sec)

`localhost.(none)>select greatest(@a,@b,@c), greatest(@d,@e,@f),`

`greatest(@a,@b,@c)+ greatest(@d,@e,@f) as total;`

+--------------------+--------------------+-------+ | greatest(@a,@b,@c) | greatest(@d,@e,@f) | total | +--------------------+--------------------+-------+ | 3 | 1 | 4 | +--------------------+--------------------+-------+ 1 row in set (0.00 sec) localhost.(none)>create database test; Query OK, 1 row affected (0.00 sec) localhost.(none)>use test Database changed localhost.test>create table t1 (a int, b int, c int, d int, e int, f int); Query OK, 0 rows affected (0.23 sec) localhost.test>insert t1 values (1,3,0,0,1,0); Query OK, 1 row affected (0.03 sec)

`localhost.test>select greatest(a,b,c), greatest(d,e,f),`

`greatest(a,b,c)+ greatest(d,e,f) as total from t1;`

+-----------------+-----------------+-------+ | greatest(a,b,c) | greatest(d,e,f) | total | +-----------------+-----------------+-------+ | 3 | 1 | 4 | +-----------------+-----------------+-------+ 1 row in set (0.00 sec)

`localhost.test>select greatest(a,b,c)+ greatest(d,e,f)+ greatest(d,e,f)`

`as total from t1;`

+-------+ | total | +-------+ | 5 | +-------+ 1 row in set (0.00 sec) Can you provide a more complete test case? Can you tell us which version of MySQL you are using? -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql

**Follow-Ups**:**Re: Adding values returned by GREATEST***From:*Peter Brawley

**References**:**Adding values returned by GREATEST***From:*Sukhjinder K. Narula

- Prev by Date:
**MySQL Connector/J 5.1.39 has been released** - Next by Date:
**Re: Adding values returned by GREATEST** - Previous by thread:
**Adding values returned by GREATEST** - Next by thread:
**Re: Adding values returned by GREATEST** - Index(es):