I have a task - search and output of min and max values ​​from two columns of the table.

An example of the values ​​of the cells of the table TABLE :

ID |S-MIN|S_MAX 0 |10 |31 1 |4 |8 2 |6 |0

Search for min and max is necessary only among the values ​​greater than zero.

I could only portray such a solution:

Script

 //ищу минимум в первом столбце $smin=mysql_fetch_array(mysql_query("SELECT `S_MIN` FROM `table` ORDER BY `S_MIN` ASC limit 1")); //ищу максимум в первом столбце $sminmax=mysql_fetch_array(mysql_query("SELECT `S_MIN` FROM `table` ORDER BY `S_MIN` DESC limit 1")); //ищу максимум во втором столбце $smax=mysql_fetch_array(mysql_query ("SELECT `S_MAX` FROM `table` ORDER BY `S_MAX` DESC limit 1")); //if нужен на случай, если максимальное значение таблицы будет лежать в первом столбце if ($smax < $sminmax) echo "Минимум=".$smin[0].", максимум=".$sminmax[0].""; else echo "Минимум=".$smin[0].", максимум=".$smax[0]."" ;

Thinking that 3 queries in the database is not good, I began to move towards optimization, and I still did not move to the end.

That's how I came to this state:

Optimization

 //извлекаем все значения талицы в массив $query = "SELECT `S_MIN`, `S_MAX` FROM `table`"; $result = mysql_query($query) or die(); $array = mysql_fetch_array($result, MYSQL_ASSOC); //сортируем все значения в массиве (при этом, как я понял происходит переиндексация) sort ($array); //и удаляем первый элемент массива, который скорее всего ноль. //сейчас понял, что нельзя просто так удалять первый элемент, ведь в таблице может и не быть нулей unset($array[0]); //дальше все просто $smin = min($array); $smax = max($array); echo "Минимум=".$smin[0].", максимум=".$smax[0]."";

Tell me where the error in my version of the optimization. And in general, is the game worth the candle?

I repeat, the first script is working.

Oh, and yes, I am not puzzled by the search for a minimum in the second column, because the data structure is such that it cannot be there by definition.

  • But what about the min and max functions in mysql itself? - andreymal
  • Yes, I know. With these functions I could not sdyuzhit, in terms of laying them in one sql query. And the question is precisely in minimizing calls to the database. - Roman Smirnov
  • and what exactly gives the first script on the data given by you? - aleksandr barakin
  • @alexanderbarakin well, now let's calculate =) smin = 4, sminmax = 10, smax = 31; The print will be min = 4, maximum = 31 - Roman Smirnov
  • Your answer coincides with the request I have proposed. so I guessed right. - aleksandr barakin

2 answers 2

You need such a query if I understood everything correctly.

update

It seems you need something different: choose the maximum value in any of the two columns, and the minimum value in any of the same two columns.

This is implemented below: request number two - using auxiliary views , request number three - without them.

SQL feeddle

MySQL 5.6 Schema Setup :

 create table t (id int, s_min int, s_max int); insert into t values (0, 10, 31) ,(1, 4, 8) ,(2, 6, 0) ,(3, -1, 50) ,(4, 35, -2) ,(5, 7, 3) ; create view v1 as select min(s_min) as r from t where s_min >0; create view v2 as select min(s_max) as r from t where s_max >0; create view v3 as select max(s_min) as r from t where s_min >0; create view v4 as select max(s_max) as r from t where s_max >0;

Query 1 :

 select min(s_min) as min, max(s_max) as max from t where s_min > 0

Results :

 | min | max | |-----|-----| | 4 | 31 |

Query 2 :

 select least(v1.r, v2.r) as min, greatest(v3.r, v4.r) as max from v1 join v2 join v3 join v4

Results :

 | min | max | |-----|-----| | 3 | 50 |

Query 3 :

 select least(v1.r, v2.r) as min, greatest(v3.r, v4.r) as max from ( select min(s_min) as r from t where s_min >0 ) as v1 join ( select min(s_max) as r from t where s_max >0 ) as v2 join ( select max(s_min) as r from t where s_min >0 ) as v3 join ( select max(s_max) as r from t where s_max >0 ) as v4

Results :

 | min | max | |-----|-----| | 3 | 50 |
  • seems to be true, tomorrow I will check in the system - Roman Smirnov
  • There is an assumption that you need to throw out zeros independently, that is, two separate requests for s_min and s_max - andreymal
  • @andreymal, in general, s_max >= 0 is not needed here at all: positive numbers are usually more negative. // removed this construct. - aleksandr barakin
  • uh, working with pseudonyms, while the feature of SQL = \ untouched by me = Besides, as far as I understand, the assignment of pseudonyms goes at the stage of creating a table? and I'm working in my script from the ready. - Roman Smirnov
  • Did you consider that two values ​​are returned in one array row? besides, the first of the requests, as far as I understand, does not suit you. - aleksandr barakin
 select min(if(s_min>0,s_min,NULL)) as smin, greatest(max(s_min),max(s_max)) as smax from `table`