Table 11.11. Mathematical Functions
Name | Description |
---|---|
ABS() |
Return the absolute value |
ACOS() |
Return the arc cosine |
ASIN() |
Return the arc sine |
ATAN2() , ATAN() |
Return the arc tangent of the two arguments |
ATAN() |
Return the arc tangent |
CEIL() |
Return the smallest integer value not less than the argument |
CEILING() |
Return the smallest integer value not less than the argument |
CONV() |
Convert numbers between different number bases |
COS() |
Return the cosine |
COT() |
Return the cotangent |
CRC32() (v4.1.0) |
Compute a cyclic redundancy check value |
DEGREES() |
Convert radians to degrees |
EXP() |
Raise to the power of |
FLOOR() |
Return the largest integer value not greater than the argument |
LN() |
Return the natural logarithm of the argument |
LOG10() |
Return the base-10 logarithm of the argument |
LOG2() |
Return the base-2 logarithm of the argument |
LOG() |
Return the natural logarithm of the first argument |
MOD() |
Return the remainder |
OCT() |
Return an octal representation of a decimal number |
PI() |
Return the value of pi |
POW() |
Return the argument raised to the specified power |
POWER() |
Return the argument raised to the specified power |
RADIANS() |
Return argument converted to radians |
RAND() |
Return a random floating-point value |
ROUND() |
Round the argument |
SIGN() |
Return the sign of the argument |
SIN() |
Return the sine of the argument |
SQRT() |
Return the square root of the argument |
TAN() |
Return the tangent of the argument |
TRUNCATE() |
Truncate to specified number of decimal places |
All mathematical functions return NULL
in the
event of an error.
Returns the absolute value of X
.
mysql>SELECT ABS(2);
-> 2 mysql>SELECT ABS(-32);
-> 32
This function is safe to use with
BIGINT
values.
Returns the arc cosine of X
, that
is, the value whose cosine is X
.
Returns NULL
if
X
is not in the range
-1
to 1
.
mysql>SELECT ACOS(1);
-> 0.000000 mysql>SELECT ACOS(1.0001);
-> NULL mysql>SELECT ACOS(0);
-> 1.570796
Returns the arc sine of X
, that
is, the value whose sine is X
.
Returns NULL
if
X
is not in the range
-1
to 1
.
mysql>SELECT ASIN(0.2);
-> 0.201358 mysql>SELECT ASIN('foo');
-> 0.000000
Returns the arc tangent of X
,
that is, the value whose tangent is
X
.
mysql>SELECT ATAN(2);
-> 1.107149 mysql>SELECT ATAN(-2);
-> -1.107149
Returns the arc tangent of the two variables
X
and
Y
. It is similar to calculating
the arc tangent of
, except that the
signs of both arguments are used to determine the quadrant
of the result.
Y
/
X
mysql>SELECT ATAN(-2,2);
-> -0.785398 mysql>SELECT ATAN2(PI(),0);
-> 1.570796
CEIL()
is a synonym for
CEILING()
. It was added in
MySQL 4.0.6.
Returns the smallest integer value not less than
X
.
mysql>SELECT CEILING(1.23);
-> 2 mysql>SELECT CEILING(-1.23);
-> -1
Note that the return value is converted to a
BIGINT
.
Converts numbers between different number bases. Returns a
string representation of the number
N
, converted from base
from_base
to base
to_base
. Returns
NULL
if any argument is
NULL
. The argument
N
is interpreted as an integer,
but may be specified as an integer or a string. The minimum
base is 2
and the maximum base is
36
. If to_base
is a negative number, N
is
regarded as a signed number. Otherwise,
N
is treated as unsigned.
CONV()
works with 64-bit
precision.
mysql>SELECT CONV('a',16,2);
-> '1010' mysql>SELECT CONV('6E',18,8);
-> '172' mysql>SELECT CONV(-17,10,-18);
-> '-H' mysql>SELECT CONV(10+'10'+'10'+0xa,10,10);
-> '40'
Returns the cosine of X
, where
X
is given in radians.
mysql> SELECT COS(PI());
-> -1.000000
Returns the cotangent of X
.
mysql>SELECT COT(12);
-> -1.57267341 mysql>SELECT COT(0);
-> NULL
Computes a cyclic redundancy check value and returns a
32-bit unsigned value. The result is NULL
if the argument is NULL
. The argument is
expected to be a string and (if possible) is treated as one
if it is not.
mysql> SELECT CRC32('MySQL');
-> 3259397556
CRC32()
is available as of
MySQL 4.1.0.
Returns the argument X
, converted
from radians to degrees.
mysql> SELECT DEGREES(PI());
-> 180.000000
Returns the value of e (the base of
natural logarithms) raised to the power of
X
. The inverse of this function
is the LOG()
. In MySQL 4.0.3
or later, its inverse is
LOG()
using a single argument
or LN()
.
mysql>SELECT EXP(2);
-> 7.3890560989307 mysql>SELECT EXP(-2);
-> 0.13533528323661 mysql>SELECT EXP(0);
-> 1
Returns the largest integer value not greater than
X
.
mysql>SELECT FLOOR(1.23);
-> 1 mysql>SELECT FLOOR(-1.23);
-> -2
Note that the return value is converted to a
BIGINT
.
Formats the number X
to a format
like '#,###,###.##'
, rounded to
D
decimal places, and returns the
result as a string. For details, see
Section 11.4, “String Functions”.
This function can be used to obtain a hexadecimal representation of a decimal number or (beginning with MySQL 4.0.1) a string; the manner in which it does so varies according to the argument's type. See this function's description in Section 11.4, “String Functions”, for details.
Returns the natural logarithm of
X
; that is, the
base-e logarithm of
X
. If
X
is less than or equal to 0,
then NULL
is returned.
mysql>SELECT LN(2);
-> 0.69314718055995 mysql>SELECT LN(-2);
-> NULL
This function was added in MySQL 4.0.3. It is synonymous
with
LOG(
.
The inverse of this function is the
X
)EXP()
function.
If called with one parameter, this function returns the
natural logarithm of X
. If
X
is less than or equal to 0,
then NULL
is returned.
The inverse of this function (when called with a single
argument) is the EXP()
function.
mysql>SELECT LOG(2);
-> 0.69314718055995 mysql>SELECT LOG(-2);
-> NULL
If called with two parameters, this function returns the
logarithm of X
to the base
B
. If
X
is less than or equal to 0, or
if B
is less than or equal to 1,
then NULL
is returned.
mysql>SELECT LOG(2,65536);
-> 16.000000 mysql>SELECT LOG(10,100);
-> 2 mysql>SELECT LOG(1,100);
-> NULL
The arbitrary base option was added in MySQL 4.0.3.
LOG(
is equivalent to
B
,X
)LOG(
.
X
) /
LOG(B
)
Returns the base-2 logarithm of
.
X
mysql>SELECT LOG2(65536);
-> 16.000000 mysql>SELECT LOG2(-100);
-> NULL
LOG2()
is useful for finding
out how many bits a number would require for storage. This
function was added in MySQL 4.0.3. In earlier versions, you
can use
LOG(
instead.
X
) /
LOG(2)
Returns the base-10 logarithm of
X
.
mysql>SELECT LOG10(2);
-> 0.301030 mysql>SELECT LOG10(100);
-> 2.000000 mysql>SELECT LOG10(-100);
-> NULL
Modulo operation. Returns the remainder of
N
divided by
M
.
mysql>SELECT MOD(234, 10);
-> 4 mysql>SELECT 253 % 7;
-> 1 mysql>SELECT MOD(29,9);
-> 2 mysql>SELECT 29 MOD 9;
-> 2
This function is safe to use with
BIGINT
values. The
syntax works only as
of MySQL 4.1.0.
N
MOD
M
As of MySQL 4.1.7, MOD()
works on values that have a fractional part and returns the
exact remainder after division:
mysql> SELECT MOD(34.5,3);
-> 1.5
Before MySQL 4.1.7, MOD()
rounds arguments with a fractional value to integers and
returns an integer result:
mysql> SELECT MOD(34.5,3);
-> 2
MOD(
returns N
,0)NULL
.
Returns a string representation of the octal value of
N
, where
N
is a longlong
(BIGINT
) number. This is
equivalent to
CONV(
.
Returns N
,10,8)NULL
if
N
is NULL
.
mysql> SELECT OCT(12);
-> '14'
Returns the value of π (pi). The default number of decimal places displayed is five, but MySQL uses the full double-precision value internally.
mysql>SELECT PI();
-> 3.141593 mysql>SELECT PI()+0.000000000000000000;
-> 3.141592653589793116
Returns the value of X
raised to
the power of Y
.
mysql>SELECT POW(2,2);
-> 4.000000 mysql>SELECT POW(2,-2);
-> 0.250000
This is a synonym for POW()
.
Returns the argument X
, converted
from degrees to radians. (Note that π radians equals 180
degrees.)
mysql> SELECT RADIANS(90);
-> 1.570796
Returns a random floating-point value
v
in the range
0
<= v
<
1.0
. If a constant integer argument
N
is specified, it is used as the
seed value, which produces a repeatable sequence of column
values. In the following example, note that the sequences of
values produced by RAND(3)
is the same
both places where it occurs.
mysql>CREATE TABLE t (i INT);
Query OK, 0 rows affected (0.42 sec) mysql>INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql>SELECT i, RAND() FROM t;
+------+------------------+ | i | RAND() | +------+------------------+ | 1 | 0.61914388706828 | | 2 | 0.93845168309142 | | 3 | 0.83482678498591 | +------+------------------+ 3 rows in set (0.00 sec) mysql>SELECT i, RAND(3) FROM t;
+------+------------------+ | i | RAND(3) | +------+------------------+ | 1 | 0.90576975597606 | | 2 | 0.37307905813035 | | 3 | 0.14808605345719 | +------+------------------+ 3 rows in set (0.00 sec) mysql>SELECT i, RAND() FROM t;
+------+------------------+ | i | RAND() | +------+------------------+ | 1 | 0.35877890638893 | | 2 | 0.28941420772058 | | 3 | 0.37073435016976 | +------+------------------+ 3 rows in set (0.00 sec) mysql>SELECT i, RAND(3) FROM t;
+------+------------------+ | i | RAND(3) | +------+------------------+ | 1 | 0.90576975597606 | | 2 | 0.37307905813035 | | 3 | 0.14808605345719 | +------+------------------+ 3 rows in set (0.01 sec)
The effect of using a nonconstant argument is undefined. As of MySQL 4.1.15, nonconstant arguments are disallowed.
To obtain a random integer R
in
the range i
<=
R
<
j
, use the expression
FLOOR(
. For example, to
obtain a random integer in the range the range
i
+ RAND() * (j
–
i
))7
<= R
<
12
, you could use the following
statement:
SELECT FLOOR(7 + (RAND() * 5));
RAND()
in a
WHERE
clause is re-evaluated every time
the WHERE
is executed.
You cannot use a column with
RAND()
values in an
ORDER BY
clause, because ORDER
BY
would evaluate the column multiple times.
However, as of MySQL 3.23, you can retrieve rows in random
order like this:
mysql> SELECT * FROM tbl_name
ORDER BY RAND();
ORDER BY RAND()
combined with
LIMIT
is useful for selecting a random
sample from a set of rows:
mysql>SELECT * FROM table1, table2 WHERE a=b AND c<d
->ORDER BY RAND() LIMIT 1000;
RAND()
is not meant to be a
perfect random generator, but instead is a fast way to
generate ad hoc random
numbers which is portable between platforms for the same
MySQL version.
Rounds the argument X
to
D
decimal places.
D
defaults to 0 if not specified.
D
can be negative to cause
D
digits left of the decimal
point of the value X
to become
zero.
mysql>SELECT ROUND(-1.23);
-> -1 mysql>SELECT ROUND(-1.58);
-> -2 mysql>SELECT ROUND(1.58);
-> 2 mysql>SELECT ROUND(1.298, 1);
-> 1.3 mysql>SELECT ROUND(1.298, 0);
-> 1 mysql>SELECT ROUND(23.298, -1);
-> 20
The return type is the same type as that of the first argument (assuming that it is integer, double, or decimal). This means that for an integer argument, the result is an integer (no decimal places).
The behavior of ROUND()
when
the argument is halfway between two integers depends on the
C library implementation. Different implementations round to
the nearest even number, always up, always down, or always
toward zero. If you need one kind of rounding, you should
use a well-defined function such as
TRUNCATE()
or
FLOOR()
instead.
Returns the sign of the argument as -1
,
0
, or 1
, depending on
whether X
is negative, zero, or
positive.
mysql>SELECT SIGN(-32);
-> -1 mysql>SELECT SIGN(0);
-> 0 mysql>SELECT SIGN(234);
-> 1
Returns the sine of X
, where
X
is given in radians.
mysql>SELECT SIN(PI());
-> 1.2246063538224e-16 mysql>SELECT ROUND(SIN(PI()));
-> 0
Returns the square root of a nonnegative number
X
.
mysql>SELECT SQRT(4);
-> 2 mysql>SELECT SQRT(20);
-> 4.4721359549996 mysql>SELECT SQRT(-16);
-> NULL
Returns the tangent of X
, where
X
is given in radians.
mysql>SELECT TAN(PI());
-> -1.2246063538224e-16 mysql>SELECT TAN(PI()+1);
-> 1.5574077246549
Returns the number X
, truncated
to D
decimal places. If
D
is 0
, the
result has no decimal point or fractional part.
D
can be negative to cause
D
digits left of the decimal
point of the value X
to become
zero.
mysql>SELECT TRUNCATE(1.223,1);
-> 1.2 mysql>SELECT TRUNCATE(1.999,1);
-> 1.9 mysql>SELECT TRUNCATE(1.999,0);
-> 1 mysql>SELECT TRUNCATE(-1.999,1);
-> -1.9 mysql>SELECT TRUNCATE(122,-2);
-> 100 mysql>SELECT TRUNCATE(10.28*100,0);
-> 1027
Starting from MySQL 3.23.51, all numbers are rounded toward zero.
User Comments
My brother had a case where he wanted to sort
randomly but ALSO use LIMIT so he could page
results - of course random will be different each time.
He wanted a random order that was not random for
the same session; so here is the idea:
In the web-side code calculate a numeric value which
is likely to stay the same for a session, perhaps
based on some session id, or timed-expiring cookie
value, etc, or from short-term stable HTTP headers.
Also require a numeric and well distributed value for
each record (doesn't have to be unique but works
well if it is).
Then:
... order by rand(numeric_field + session_value)
LIMIT blah;
So we see the ordering is preserved as
numeric_field+session_value will be the same for a
session, and numeric_field + session value are NOT
the same from row to row so we still get random
ordering.
Sam Liddicott
I might be caused by compiler ability to count to upto 30 places under zero. Win32 mysql probably mighe be compiled with 32bit compiler rather than 64bit. -- Hyungjin Ahn(ahj6@hotmail.com)
You may need to compare columns in databases after converting say a string column to a numeric column. These comparisons are automatic
Example
in the WHERE clause you may have to do something like this
oem.oem_id=substring(sku,5,3)
Here sku is a string who substring starting from location 5 from left and then having total length of 3 is compared with a numeric value of oem_id to satisfy the WHERE clause.
For more details see
http://www.bitmechanic.com/mail-archives/mysql/May1997/0494.html
I wanted to round to the nearest 0 or 5 cents in currency and this query worked:
select round((((cost*100) - (cost*100)%5) /100), 2) from SessionCost;
If "SELECT * FROM tab ORDER BY RAND()" doesn't work for you. Try to put a random value between the brackets.
Here is my work around for MySQL rounding issues (On most systems it rounds to the nearest even number on 5). This mess of a calculation will round up always in mysql, which is how most people in the united states think about rounding:
num = the number you are rounding
ROUND( TRUNCATE(num,2) + REPLACE( ( (num*1000) - ( TRUNCATE(num,2) *1000) / 1000, '5', '6'), 2)
This example rounds to 2 decimal places. If you want to round to three decimals just switch out the 2s for 3s and the 1000s for 10000s, etc.
It basically works by replacing all the fives beyond the two decimal places with sixes, which will always round up. Then calling the round function.
Another way to round up to two decimals is using the following formula:
floor(num * 100 + .55)/100
This may be self-evident but:
In a list where some elements had priority and others not I needed to randomise the prioritised items and not the rest. The prioritised entries all had a value of 1 in a field called 'enhanced' and all entries had an abbreviated name ('abbrev') that they were otherwise sorted by. Using
ORDER BY (RAND() * enhanced) desc, abbrev
I could change the order of the enhanced listings yet maintain an alphabetical listing thereafter.
WARNING WITH ROUND AND FORMAT FUNCTIONS:
As mentioned in the manual, ROUND function has problems with values near to the limit values. The same prblem is found in the format function Let's see it:
round(1.15,1)=1.2 OK
round(1.25,1)=1.2 BAD, sould be 1.3
round(1.35,1)=1.4 OK
round(1.45,1)=1.4 BAD, sould be 1.5
round(1.55,1)=1.6 OK
And so on...
A walkarround for this sould be to use truncate adding 0,06. The same problem in found in the format function.
Of corse, if you want to use more than one digit, you should add as many 0 as you need to de value added in the truncate function. Note that in case of using 2 digits, the result of format is correct but round stills failing. It is more reliable to do the calculation using your own formula, with truncate.
I think the "bads" are actually bankers rounding
As truncate comment above, but negative number safe:
Take special care when using the the unsafe version with grouping functions like SUM(), as the end result can be way off if there is a big mix of negative/positive numbers.
sign(num) * truncate(abs(num)+0.06,1)
E.g...
I finally had to come up with my own solution for rounding with currency in the U.S.
Most of us consider this:
25.725 to be 25 dollars and 73 cents
But mysql was returning: round(25.725,2) as 25.72 which was throwing off my calc.
So, my workaround after not finding a solution is:
if num=25.725
============================
truncate(num + 0.0051,2)
============================
will yield this result: 25.73, which is correct.
I hope this helps someone else.
Ken
The rounding functions above are a little bit off from what most people would consider standard rounding.
If you use 6 as the number you are adding to the digit beyond significance then you will be rounding up 0.4s as well as 0.5s.
Here is my method:
rounding to two decimals
TRUNCATE(num + (SIGN(num) * 0.005), 2)
example 1
TRUNCATE(0.004 + (SIGN(0.004) * 0.005),2) = TRUNCATE(0.009,2) = 0.00
example 2
TRUNCATE(0.005 + (SIGN(0.005) * 0.005),2) = TRUNCATE(0.010,2) = 0.01
for three decimals it would be
TRUNCATE(num + (SIGN(num) * 0.0005), 3)
etc.
BTW this seems to be how PHP's round function works, so if you are trying to get calculations in PHP to match MySQL this is how I did it.
Am I mistaken about the command for an integer ranged RAND function...
Given what is printed here:
FLOOR(i + RAND() * (j - i))
I only ever get results in the range of i to j-1.
Shouldn't it be
FLOOR(i + RAND() * (j - i + 1 )) ?
I am getting results in the range I need with that. Maybe I am missing something, maybe once in a great while there will be a result that is j+1 and I have just not seen it.
BTW, I am using it as:
CREATE FUNCTION IRAND(param1 INT, param2 INT) RETURNS INT
RETURN FLOOR(param1 + RAND() * (param2-param1+1)) ;
That is true. The RAND() function returns a value 0.0 <= x <= 1.0
Thus, the values '0.0' and '1.0' can be returned althoug the changes are very very little.
In the example, where one wants a value between 7 and 12 inclusive, the value of '12' will hardly ever be returned.
I wanted a value of '0' or '1' (i.e. yes or no), so I used FLOOR(RAND() + 0.5), cuz if I'd used FLOOR(i + RAND() * (j – i), i.e. FLOOR(0 + RAND() * (1 – 0)) which evaluates to (FLOOR(RAND()), I would have gotten only one '1' and a trillillizillion 0's.
ROUND(X,Y)
ok i experienced like the description says different behaviour on rounding on different systems
so based on the examples by other ppl who might work for their issue but are neither save nor a
general purpose solution i have come up with my own solution for rounding up on 5
the number of decimal places you want : X
number : Y
general solution :
TRUNCATE((Y+SIGN(Y)*(POW(10,(1-X))/18)),X)
example (the other solutions fail here) :
y = 12.449
x = 1
result : 12.5
hope this helps you too
Simple but effective function for rounding to two decimals correctly (eg. 0.625 rounds to 0.63), unlike with the broken 'round' function
CREATE FUNCTION `v_round`(round_me DOUBLE)
RETURNS decimal(10,2)
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
return round_me;
END;
Since using MySQL's RAND() function on a large rowset is notoriously slow:
To quickly select a random row, basically, do it in two SELECTS:
1. first SELECT finds out number of rows available, usnig a WHERE clause if desired.
2. web code chooses a random row from the number of rows (from step 1.) and saves this number in $x.
3. second SELECT (using the same WHERE clause in step 1.) uses LIMIT 1,$x.
Actually Order by Rand() Limit(1,X) won't work on larger sets, as it has to read through X-1 records to return the 1 you need.
In avarage it means reading through <NumberOfRecords>/2 records every time, thus it's slow.
Yes, using limit is a silly way of doing that. Why not just select with id = the random id you picked?
Here's some Ruby:
max = dbh.query("select max(id) from table").fetch_row.first
rand_id = rand(max)
row = dbh.query("select * from table where id = #{rand_id}").fetch_hash
puts "Fetched: #{row['id']}"
Nate:
Doing it that way doesn't work for everyone, some id values less than the max id might no longer exist in the table due to deletes.
Here's a simple solution (in php):
mysql_query('START TRANSACTION');
$count=mysql_fetch_row(mysql_query('SELECT COUNT(*) FROM table'));
$randomRow=mysql_fetch_row(mysql_query('SELECT * FROM table LIMIT '.(mt_rand(0,$count[0]-1)).',1'));
mysql_query('ROLLBACK');
Haven't really tested it, but you'd get the idea.
It's similar to michael's idea (posted above), only he had the limit parameters in the wrong order.
Instead of using RAND and LIMIT tricks for randomness, with their limitations on speed, if you have a primary key that's an auto-incrementer, you could do it with these two SELECT like so:
SELECT MAX(pkey) FROM articles;
...then grab a random number (shown as $r below) between 1 and max in your code. Now return back to SQL like so:
SELECT * FROM articles where pkey > $r LIMIT $limit;
...where $limit is the number of rows you want to likely return.
...Then, to create the illusion of more randomness, just use an ORDER BY clause on the second SELECT above based on something arbitrary. For instance, if 'articles' has a column like author name and another like category, you could change the SELECT statement above like:
SELECT * FROM articles where pkey > $r ORDER BY category, name LIMIT $limit;
...So, by using this strategy, it's faster than having to randomly determine your pkeys and selecting only one record at a time.
In my case, I wanted to sort classified listings with some close approximation of randomness in order to rotate the listings, and this strategy has worked for me.
After I had problems with the ROUND() function in an accounting application where i need commercial rounding I wrote this stored function that works very well for my needs:
CREATE FUNCTION ROUND_COMMERCIAL(value DOUBLE, preci INT(11)) RETURNS DOUBLE NO SQL
RETURN TRUNCATE((value * POW(10, preci)) + (IF(value = 0, 1,(value / ABS(value)))*(0.5 * POW(1, preci*-1))), 0) / POW(10, preci);
If you want to select more records randomly you can use following method:
SET @toGet=10;
SET @left=(SELECT COUNT(*) FROM tableName)+1;
SELECT *, @toGet:=@toGet-1
FROM tableName
WHERE (@left:=@left-1)>0 AND RAND()<@toGet/@left;
It's much faster than ORDER BY RAND() LIMIT 10 (especially if you want to fetch small random subset of rows stored in table) but if it happens to return same set of rows, it returns them always in same order. If you want them to have random order then you have to scramble them after fetching using subquery:
SET @toGet=10;
SET @left=(SELECT COUNT(*) FROM tableName)+1;
SELECT * FROM (
SELECT *, @toGet:=@toGet-1
FROM tableName
WHERE (@left:=@left-1)>0 AND RAND()<@toGet/@left
) t ORDER BY RAND()
or on client side.
Oddly truncate produces 2 different results for the same mathematical function with variations in placement of truncate in relation to a conditional:
mysql> select version(),if(2.268>1.249,truncate(ceil(((2.268-1.249)/6+.01)*100)/100,2),'0.00') as fscpm;
mysql> select version(),truncate(if(2.268>1.249,ceil(((2.268-1.249)/6+.01)*100)/100,'0.00'),2) as fscpm;
Nearest even rounding is not bad. In fact, it results in less statistically biased results than always rounding up.
If you reached this page looking for functions like MIN(a,b,...) and MAX(a,b,...) they are named LEAST()and GREATEST(), and are in section 11.2.3. Comparison Functions and Operators.
For those of you who need to implement banker's rounding in MySQL (handy if you're doing invoice reports and the numbers need to match up with accounting software like Simply Accounting that use banker's rounding), this is what I use:
CREATE FUNCTION BROUND( value DECIMAL(65,30), places TINYINT(3) UNSIGNED ) RETURNS DECIMAL(65,30) DETERMINISTIC RETURN
CASE WHEN
LOCATE( '.', value ) >= 1
AND LENGTH( value ) < 33
AND places > 0
AND LENGTH( value ) - LOCATE( '.', value ) > places
AND SUBSTRING( value, LOCATE( '.', value ) + places + 1, 1 ) = 5
AND SUBSTRING( value, LOCATE( '.', value ) + places, 1 ) % 2 = 0
THEN
SUBSTRING( value, 1, LOCATE( '.', value ) + places )
ELSE
ROUND( value, places )
END;
I've tested it with numbers like 0.051525354555657585950515253545 and it appears to operate properly to 30 decimal places, after this it falls back to the MySQL built-in ROUND() function.
Do your own testing first of course.
on my previous comment, it appears to short you one decimal place when doing negative numbers, you may want to (easily) correct this by ORing the length < 33 condition to be 34 for negative values.
Add your own comment.