Getting Maximum Value of Float in SQL Programmatically

Getting maximum value of float in SQL programmatically

Though there doesn't appear to be any inline way to get the min or max values, there's a solution somebody put together:

 CREATE TABLE datatype_extrema 
  (min_bit bit NOT NULL DEFAULT (0) CHECK (min_Bit=0) 
  ,max_bit           AS CAST(0x1 AS bit) 
  ,min_tinyint       AS CAST(0x00 AS tinyint) 
  ,max_tinyint       AS CAST(0xFF AS tinyint) 
  ,min_smallint      AS CAST(0x8000 AS smallint) 
  ,max_smallint      AS CAST(0x7FFF AS smallint) 
  ,min_int           AS CAST(0x80000000 AS int) 
  ,max_int           AS CAST(0x7FFFFFFF AS int) 
  ,min_bigint        AS CAST(0x8000000000000000 AS bigint) 
  ,max_bigint        AS CAST(0x7FFFFFFFFFFFFFFF AS bigint)
  ,min_float         AS CAST('-1.79E+308' AS float)
  ,max_float         AS CAST('1.79E+308' AS float)
  ,min_real          AS CAST('-3.40E+38' AS real)
  ,max_real          AS CAST('3.40E+38' AS real)
  ,min_smalldatetime AS CAST('19000101 00:00' AS smalldatetime) 
  ,max_smalldatetime AS CAST('20790606 23:59' AS smalldatetime) 
  ,min_datetime      AS CAST('17530101 00:00:00.000' AS datetime) 
  ,max_datetime      AS CAST('99991231 23:59:59.997' AS datetime) 
  )
  INSERT INTO datatype_extrema DEFAULT VALUES 
  GO 
  CREATE TRIGGER nochange_datatype_extrema 
  ON datatype_extrema INSTEAD OF INSERT, UPDATE, DELETE 
  AS BEGIN 
    RAISERROR ('No changes allowed for table datatype_extrema.', 16, 1) 
    ROLLBACK TRANSACTION 
  END 
  GO 

After that, you can either copy a maximum value to a local variable or
(when using queries) cross join with this table.

  Declare @max_int int 
  Set @max_int=(SELECT max_int FROM datatype_extrema) 
  IF COALESCE(@FirstInt, @max_int) < COALESCE(@SecondInt, 0) 

max value represented by bigint

See the answer provided in this similar question. There is no way, as far as I know, to programmatically find the answer you're looking for.

Based on the comments you posted on another answer, this would allow you to only have to change your values in one place, as opposed to multiple places.

Why does MySQL round floats way more than expected?

When you run the query:

SELECT * FROM some_table WHERE id = 123

You are relying on the user interface to format the floating point numbers. The interface you are using is using two characters rather than more. After all, there is no information on the "right" number to show.

You can convince the interface to show the right number by formatting the number as a string or as a decimal. For instance:

select format(some_float_field, 3)

will convert this to a string with three decimal places. One caution: it will also add commas which you might not want. This should also work:

select cast(some_float_field as decimal(8, 3))

Note that you can readily validate that the data is correct by doing something like:

select *
from some_table
where some_float_field between 1919.987 - 0.0001 and 1919.987 + 0.0001;

Note that you don't want to use = on floating point values, but you already understand that.

SQL: converting a column of numeric and null values to FLOAT

You can use this

SELECT CONVERT(float, CASE WHEN ISNUMERIC(columnName) = 1 THEN columnName ELSE NULL END) FROM TableABC

Rounding off the Float to highest

Use Math.ceil(5.3) to round it to 6. It will return Double then you can use toInt() on it. Like this:

val value = 106.div(20.00)
val finalValue = Math.ceil(value).toInt()

Is there a method to find the max of 3 numbers in C#?

Well, you can just call it twice:

int max3 = Math.Max(x, Math.Max(y, z));

If you find yourself doing this a lot, you could always write your own helper method... I would be happy enough seeing this in my code base once, but not regularly.

(Note that this is likely to be more efficient than Andrew's LINQ-based answer - but obviously the more elements you have the more appealing the LINQ approach is.)

EDIT: A "best of both worlds" approach might be to have a custom set of methods either way:

public static class MoreMath
{
    // This method only exists for consistency, so you can *always* call
    // MoreMath.Max instead of alternating between MoreMath.Max and Math.Max
    // depending on your argument count.
    public static int Max(int x, int y)
    {
        return Math.Max(x, y);
    }

    public static int Max(int x, int y, int z)
    {
        // Or inline it as x < y ? (y < z ? z : y) : (x < z ? z : x);
        // Time it before micro-optimizing though!
        return Math.Max(x, Math.Max(y, z));
    }

    public static int Max(int w, int x, int y, int z)
    {
        return Math.Max(w, Math.Max(x, Math.Max(y, z)));
    }

    public static int Max(params int[] values)
    {
        return Enumerable.Max(values);
    }
}

That way you can write MoreMath.Max(1, 2, 3) or MoreMath.Max(1, 2, 3, 4) without the overhead of array creation, but still write MoreMath.Max(1, 2, 3, 4, 5, 6) for nice readable and consistent code when you don't mind the overhead.

I personally find that more readable than the explicit array creation of the LINQ approach.

How to determine the max precision for double

@PeterLawrey states max precision in 15.

That's actually not what he stated at all. What he stated was:

double has 15 decimal places of accuracy

and he is wrong. They have 15 decimal digits of accuracy.

The number of decimal digits in any number is given by its log to the base 10. 15 is the floor value of log₁₀(2⁵³-1), where 53 is the number of bits of mantissa (including the implied bit), as described in the Javadoc and IEEE 754, and 2⁵³-1 is therefore the maximum possible mantissa value. The actual value is 15.954589770191003298111788092734 to the limits of the Windows calculator.

He is quite wrong to describe it as 'decimal places of accuracy'. A double has 15 decimal digits of accuracy if they are all before the decimal point. For numbers with fractional parts you can get many more than 15 digits in the decimal representation, because of the incommensurability of decimal and binary fractions.

Get min and max integer value containing X number of digits

Fox any x>1, the min value would be 10^x-1. E.g.:

• If x = 2, min=10^2-1 = 10¹ = 10
• If x = 3, min=10^3-1 = 10² = 100
• If x = 4, min=10^4-1 = 10³ = 1000

For x=1, since it's the only value that is not a one followed by a series of zeroes, you'll need special treatment.

public static int MinIntWithXDigits(this int x)
{
    if (x == 0) 
    {
        throw new ArgumentException(nameof(x), "An integer cannot contain zero digits.");
    }

    if (x == 1) {
        return 0;
    }

    try
    {
        return Convert.ToInt32(Math.Pow(10, x - 1));
    }
    catch 
    {
        throw new InvalidOperationException($"A number with {x} digits cannot be represented as an integer.");
    }
}

Side note:

If the result were restricted to positive integers instead of non-negative integers, the smallest integer with one digit would be 1, and the general formula would have applied for this case too.

---

Related Topics