Fetch the maximum value from a MySQL column?

To fetch the maximum value from a MySQL column in C#, you need to establish a database connection and execute a query using either the MAX() function or ORDER BY with LIMIT. This tutorial demonstrates both approaches using MySQL.Data.MySqlClient.

Syntax

Following is the syntax for fetching maximum value using the MAX() function −

SELECT MAX(column_name) FROM table_name;

Following is the syntax using ORDER BY with LIMIT −

SELECT column_name FROM table_name ORDER BY column_name DESC LIMIT 1;

Database Setup

First, let us create a table and insert sample data −

mysql> create table DemoTable
   -> (
   -> Value int
   -> );
Query OK, 0 rows affected (0.63 sec)

mysql> insert into DemoTable values(78);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values(89);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values(98);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values(58);
Query OK, 1 row affected (0.25 sec)

mysql> select * from DemoTable;
+-------+
| Value |
+-------+
| 78    |
| 89    |
| 98    |
| 58    |
+-------+
4 rows in set (0.00 sec)

Using MAX() Function

The MAX() function is the most efficient way to get the maximum value from a column −

using System;
using MySql.Data.MySqlClient;

class Program {
   public static void Main() {
      string connectionString = "server=localhost;database=testdb;uid=root;pwd=password;";
      
      using (MySqlConnection connection = new MySqlConnection(connectionString)) {
         try {
            connection.Open();
            string query = "SELECT MAX(Value) as MaxValue FROM DemoTable";
            
            using (MySqlCommand command = new MySqlCommand(query, connection)) {
               object result = command.ExecuteScalar();
               
               if (result != DBNull.Value && result != null) {
                  Console.WriteLine("Maximum value using MAX(): " + result);
               } else {
                  Console.WriteLine("No data found or column contains only NULL values");
               }
            }
         } catch (Exception ex) {
            Console.WriteLine("Error: " + ex.Message);
         }
      }
   }
}

The output of the above code is −

Maximum value using MAX(): 98

Using ORDER BY with LIMIT

Alternatively, you can use ORDER BY with LIMIT to get the maximum value −

using System;
using MySql.Data.MySqlClient;

class Program {
   public static void Main() {
      string connectionString = "server=localhost;database=testdb;uid=root;pwd=password;";
      
      using (MySqlConnection connection = new MySqlConnection(connectionString)) {
         try {
            connection.Open();
            string query = "SELECT Value FROM DemoTable ORDER BY Value DESC LIMIT 1";
            
            using (MySqlCommand command = new MySqlCommand(query, connection)) {
               object result = command.ExecuteScalar();
               
               if (result != DBNull.Value && result != null) {
                  Console.WriteLine("Maximum value using ORDER BY: " + result);
               } else {
                  Console.WriteLine("No records found in the table");
               }
            }
         } catch (Exception ex) {
            Console.WriteLine("Error: " + ex.Message);
         }
      }
   }
}

The output of the above code is −

Maximum value using ORDER BY: 98

Complete Example with Error Handling

Following is a comprehensive example that handles multiple scenarios −

using System;
using MySql.Data.MySqlClient;

class MaxValueFetcher {
   private string connectionString = "server=localhost;database=testdb;uid=root;pwd=password;";
   
   public void GetMaxValueWithMAX() {
      using (MySqlConnection connection = new MySqlConnection(connectionString)) {
         try {
            connection.Open();
            string query = "SELECT MAX(Value) as MaxValue FROM DemoTable";
            
            using (MySqlCommand command = new MySqlCommand(query, connection)) {
               object result = command.ExecuteScalar();
               
               if (result != DBNull.Value && result != null) {
                  Console.WriteLine("MAX() Function Result: " + result);
               } else {
                  Console.WriteLine("No data found");
               }
            }
         } catch (MySqlException ex) {
            Console.WriteLine("MySQL Error: " + ex.Message);
         }
      }
   }
   
   public void GetMaxValueWithOrderBy() {
      using (MySqlConnection connection = new MySqlConnection(connectionString)) {
         try {
            connection.Open();
            string query = "SELECT Value FROM DemoTable ORDER BY Value DESC LIMIT 1";
            
            using (MySqlCommand command = new MySqlCommand(query, connection)) {
               object result = command.ExecuteScalar();
               
               if (result != DBNull.Value && result != null) {
                  Console.WriteLine("ORDER BY Result: " + result);
               } else {
                  Console.WriteLine("No records found");
               }
            }
         } catch (MySqlException ex) {
            Console.WriteLine("MySQL Error: " + ex.Message);
         }
      }
   }
   
   public static void Main() {
      MaxValueFetcher fetcher = new MaxValueFetcher();
      fetcher.GetMaxValueWithMAX();
      fetcher.GetMaxValueWithOrderBy();
   }
}

The output of the above code is −

MAX() Function Result: 98
ORDER BY Result: 98

Comparison of Methods

Conclusion

To fetch the maximum value from a MySQL column in C#, use the MAX() function for better performance or ORDER BY DESC LIMIT 1 when you need the complete record. Always use proper connection management with using statements and handle exceptions appropriately.