Get the largest data from different fields - SQL

Question

Get the largest data from different fields - SQL

Navigation

#1 by (5 votes)
#2 by (0 votes)

2

I have a table with 'STOCK_TIENDA1'-' STOCK_TIENDA2 'AND' STOCK_TIENDA3 ', I need to take the stock of the store that has the most value and pass it to a new variable 'SHOP_MAYOR' would be something like this:

I thought of something like this:

 SET  VALOR_MAXIMO = (SELECT MAX(T.TIENDA)
 FROM (
 SELECT STOCK_TIENDA1 FROM TB1 
 UNION
 SELECT STOCK_TIENDA2 FROM TB1
 UNION
 SELECT STOCK_TIENDA3 FROM TB1

) T);

But it does not work because the fields have different names and then enter it in the MAX () function.

I hope you can help me.

mysql sql stored-procedure

asked by Francisco Acevedo 28.06.2018 в 21:35

source

2 answers

0

also serves the response of @gbianchi:

 SET  VALOR_MAXIMO = (SELECT MAX(T.stock)
 FROM (
 SELECT STOCK_TIENDA1 as stock FROM TB1 
 UNION
 SELECT STOCK_TIENDA2 as stock FROM TB1
 UNION
 SELECT STOCK_TIENDA3 as stock FROM TB1

 ) T);

answered by 28.06.2018 в 22:30

Initial value of input type file from a FormControl How do lists work in c #?

score 5 · Accepted Answer

You do not need to join the same table several times, in MySQL there is a function to obtain the highest value among several values, it is the function GREATEST , which according to the documentation:

With two or more arguments, returns the largest (maximum-valued)   argument.

Returns the largest argument among several arguments.

For example, here you get the largest for each row:

SET  TIENDA_MAYOR = GREATEST(STOCK_TIENDA1, STOCK_TIENDA2, STOCK_TIENDA3) 
FROM TB1  WHERE ...

If you want the largest of the entire table between those three columns:

SET  TIENDA_MAYOR = MAX(GREATEST(STOCK_TIENDA1, STOCK_TIENDA2, STOCK_TIENDA3)) 
FROM TB1 ...

Very important: It's not NULL safe

Now, there is an important warning in the documentation that it is necessary not to ignore:

GREATEST() returns NULL if any argument is NULL .

GREATEST() returns NULL if any argument is NULL .

It's a very common problem in MySQL, but it's very easy to make our query NULL safe by using COALESCE :

SET  TIENDA_MAYOR = GREATEST(
                              COALESCE(STOCK_TIENDA1,0), 
                              COALESCE(STOCK_TIENDA2,0), 
                              COALESCE(STOCK_TIENDA3,0)
                             ) 
FROM TB1  WHERE ...

Or by using IFNULL :

SET  TIENDA_MAYOR = GREATEST(
                              IFNULL(STOCK_TIENDA1,0), 
                              IFNULL(STOCK_TIENDA2,0), 
                              IFNULL(STOCK_TIENDA3,0)
                             ) 
FROM TB1  WHERE ...

What COALESCE or IFNULL will do is convert the value to 0 when it is NULL , thus preventing the result from being nulified when a single value appears NULL . In short, MySQL is not democratic in many functions :)