In SQL How to Get the Maximum Value for an Integer

In SQL how do I get the maximum value for an integer?

In Mysql there is a cheap trick to do this:

mysql> select ~0;
+----------------------+
| ~0                   |
+----------------------+
| 18446744073709551615 |
+----------------------+

the tilde is the bitwise negation. The resulting value is a bigint. See: http://dev.mysql.com/doc/refman/5.1/en/bit-functions.html#operator_bitwise-invert

For the other integer flavours, you can use the right bitshift operator >> like so:

SELECT ~0 as max_bigint_unsigned
,      ~0 >> 32 as max_int_unsigned
,      ~0 >> 40 as max_mediumint_unsigned
,      ~0 >> 48 as max_smallint_unsigned
,      ~0 >> 56 as max_tinyint_unsigned
,      ~0 >> 1  as max_bigint_signed
,      ~0 >> 33 as max_int_signed
,      ~0 >> 41 as max_mediumint_signed
,      ~0 >> 49 as max_smallint_signed
,      ~0 >> 57 as max_tinyint_signed
\G

*************************** 1. row ***************************
   max_bigint_unsigned: 18446744073709551615
      max_int_unsigned: 4294967295
max_mediumint_unsigned: 16777215
 max_smallint_unsigned: 65535
  max_tinyint_unsigned: 255
     max_bigint_signed: 9223372036854775807
        max_int_signed: 2147483647
  max_mediumint_signed: 8388607
   max_smallint_signed: 32767
    max_tinyint_signed: 127
1 row in set (0.00 sec)

Select MAX INT value of TEXT column

Your query is returning 3 because it is the largest value considering lexicographic order (anything starting with 3 is considered greater than anything starting with 1, just like something starting with b is greater than anything starting with a).

Use the VAL function to convert the TEXT columns into numeric values:

SELECT MAX(VAL(UserId)) AS UserId FROM UserLogin

If you're concerned about performance, you should make this column numeric, though. Take into account you're calling a function for every row in the table. Also, it won't be using any indexes this column may have.

How to get max() of integer value from a varchar field in codeigniter?

First, make sure column rf_name data type should be integer/float, not varchar/string/text etc.

So, according to the data type of the column, it will not store any string value

$maxid = $this->db->query('SELECT MAX(rf_name) AS maxid FROM table')->row()->maxid;

OR

$this->db->select_max('rf_name');
$query = $this->db->get('table');

If you have both string and numeric values you have to do the following steps

1. Get all values of column

   $this->db->select('rf_name');
   $this-db->from('table');
   $values = $this->db->get('table')->result_array(); 
   

2. Get only values from an array

   $only_values= array();
   foreach($values as $value){
      if (is_numeric($value['rf_name'])) {
         array_push($only_values, $value['rf_name']);
      }
   }
   

3. use max() and pass the array of values to get maximum numeric value from it.

   $max_value = max($only_values); 
   

How to get max integer value of a column in java sql?

As i mentioned in comments, you should not store two different information in a single column. Consider changing the table structure.

With this structure to get the result, first filter out the rows with alphabet on it. Use REGEXP to filter the records which has only numeric values. Then take the max value out of it.

SELECT MAX(PHONE) FROM db.stb WHERE col1 REGEXP '^[0-9]+$';

REGEX concept taken from this question : Detect if value is number in MySQL

Find max value in sql table

Try this one

SELECT val,
       Cast(Substring_index(val, '-', -1) AS UNSIGNED) AS valOrder
FROM   temp
ORDER  BY valorder DESC
LIMIT  1  

or this one.

SELECT val
FROM   temp
ORDER  BY Cast(Substring_index(val, '-', -1) AS UNSIGNED) DESC
LIMIT  1;

Online Demo: http://www.sqlfiddle.com/#!9/dda898/22/0

---

<sup>Further readings
- SUBSTRING_INDEX() Function - link 01, link 02, link 03

- UNSIGNED and SIGNED in MySQL - link 01, link 02</sup>

How to get MAX() value and Record Primary Key where the value occurs in a Database?

So I found this solution. This would give the Primary Key or the record ID. It also allows for the possibility that there is more than one record with the MAX() or MIN() value.

SELECT ID, Name, Hours AS [Max] FROM Professions
    WHERE Hours = (SELECT MAX(Hours) FROM Professions)
    ORDER BY ID ASC

Here is proof using another database, though the same concept applies.

I actually wrote this code while working on a completely different problem.

---

Related Topics