Mysql function creation and selection of middle value among columns

Question

I need to create a function in Mysql that should do the following things:

select 3 columns value from a table in the following way:

SELECT column1, column2, column3 FROM table WHERE id = value;
sort values of these columns, in order to know the greatest, the middle and the least.
return the value: (greatest+middle-least)/3

My question is doublefold:

1) is there a way to obtain the middle value, as there are GREATEST() and LEAST()?

2) why the following function return always the following error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2); END $$ DELIMITER' at line 27

DELIMITER $$

CREATE FUNCTION abc_value(partita INT) 
RETURNS DOUBLE DETERMINISTIC
BEGIN
DECLARE a, b, c, column1, column2, column3 DOUBLE;
DECLARE a CURSOR FOR SELECT GREATEST(column1, column2, column3) FROM table where id = value;
DECLARE c CURSOR FOR SELECT LEAST(column1, column2, column3) FROM table where id = value;
DECLARE column1 CURSOR FOR SELECT column1 FROM table where id = value;
DECLARE quotax CURSOR FOR SELECT column2 FROM table where id = value;
DECLARE quota2 CURSOR FOR SELECT column3 FROM table where id = value;
IF column1<> a THEN
   IF column1<> b THEN 
      SET c = column1;
   END IF;
END IF;
IF column2<> a THEN
   IF column2<> b THEN 
      SET c = column2;
   END IF;
END IF;
IF column3<> a THEN
   IF column3<> b THEN 
      SET c = column3;
   END IF;
END IF;
RETURN ROUND((a+b-c)/3),2);
END$$
DELIMITER;

If by middle value you mean `median`, then there is no built-in MySQL function to do that calculation. If you mean `average`, then there is. — Gordon Linoff, Sep 02 '13 at 14:55

score 0 · Answer 1 · answered Sep 02 '13 at 14:33

0

1) If you have 3 items and want to chose the middle one do something like this:

SELECT
    value
FROM
    table
ORDER BY
    value
LIMIT 1,1 --index, amount (index starts from 0, so 1 is the second record)

answered Sep 02 '13 at 14:33

Andrius Naruševičius

7,659
6
44
72

If I understand well, your solution is good when you have three values from a single column. Otherwise, I have a row with three columns and I want to select the median from them. But if I don't understand well, please let me know. – BeeBee Sep 03 '13 at 07:48
Yes, my example is to take the middle one. For median - check [this](http://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql) answer. – Andrius Naruševičius Sep 03 '13 at 07:55

Mysql function creation and selection of middle value among columns

1 Answers1