1

How to limit the value of X at 100.0000 and not let it go to 105.8968?

SELECT ((15898 / (15898 + 401 + 36) * 100) + (8572 * 0.001)) AS `X`
Kalle H. Väravas
  • 3,579
  • 4
  • 30
  • 47

2 Answers2

3

Use LEAST function:

SELECT (LEAST(100, 
              (15898 / (15898 + 401 + 36) * 100) + (8572 * 0.001)
             )
       ) AS `X`

Additional: To find maximum (reverse of Least), you can use GREATEST function

From documentation of Least():

LEAST(value1,value2,...)

With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:

If any argument is NULL, the result is NULL. No comparison is needed.

If all arguments are integer-valued, they are compared as integers.

If at least one argument is double precision, they are compared as double-precision values. Otherwise, if at least one argument is a DECIMAL value, they are compared as DECIMAL values.

If the arguments comprise a mix of numbers and strings, they are compared as numbers.

If any argument is a nonbinary (character) string, the arguments are compared as nonbinary strings.

In all other cases, the arguments are compared as binary strings.

The return type of LEAST() is the aggregated type of the comparison argument types.

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • This will invoke (potentially expensive) type juggling, I think. @GordonLinoff answer should not, I think. – bishop Sep 20 '18 at 18:11
  • @bishop from documentation: "If at least one argument is double precision, they are compared as double-precision values. Otherwise, if at least one argument is a DECIMAL value, they are compared as DECIMAL values." So I dont think so, it will have type juggling issues. Ready to be corrected if I am wrong! – Madhur Bhaiya Sep 20 '18 at 18:17
  • Yes, "compared as double precision" means all those that are _not_ double precision will be type juggled _into_ double precision. This has a cost. Perhaps inconsequential, but perhaps not. – bishop Sep 20 '18 at 18:20
2

Use LEAST():

SELECT LEAST((15898 / (15898 + 401 + 36) * 100) + (8572 * 0.001), 100.0000) AS `X`
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786