How to Find the Maximum of Multiple Columns in SQLite?

SQLite is a serverless architecture that does not require any server to perform operations and queries. It is widely used in embedded systems, mobile applications, and small–scale web applications because of its simplicity, efficiency, and portability. SQLite supports most of the standard SQL features and is highly reliable and efficient for managing small to medium-sized databases. In this article, we will learn about How to find the maximum of multiple columns in SQLite using various methods and implementation of examples and so on.

Introduction to Max Function in SQLite

The MAX function in SQLite is a powerful aggregate function used to retrieve the maximum value from a set of values within a specified column. It is particularly useful for analyzing data and finding the highest value in a dataset. This function can be applied to numeric, text, or date/time data types, making it versatile for various types of analysis.

Ways to Find the Maximum of Multiple Columns in SQLite

Let’s start by creating the table and inserting some sample values in the table. The following code creates the SampleTable and inserts four entries in the table

Query:

CREATE TABLE SampleTable 
(
    id INT PRIMARY KEY,
    column1 INT,
    column2 INT
);

INSERT INTO SampleTable (id, column1, column2)
VALUES
    (1, 10, 20),
    (2, 25, 15),
    (3, 5, 40),
    (4, 30, 10);

Output:

SampleTable

We are going to have a look at two methods in this article to go about finding the maximum of multiple columns of the table.

Method 1: Using CASE Expression

The CASE expression allows the user to write conditions much like if-else or switch statements in SQLite. It is used to create condition when one column is greater than every other.

The following query does the trick to find the maximum of two columns.

Query:

SELECT
    id,
    column1,
    column2,
    CASE
      WHEN column1 > column2 THEN column1
      ELSE column2
    END AS max_value
FROM
    SampleTable;

Output:

Output

Explanation: As we can already see if the number of columns from which we have to compare increases, the code becomes very complicated. The next method solves this issue.

Method 2: Using MAX() Function

The MAX() function returns the maximum value of all the arguments with the number of arguments can be anything. So using the MAX() function we can compare literal values as well as columns.

The following query compares the two columns that we have and returns the result as before.

Query:

SELECT
    id,
    column1,
    column2,
    MAX(column1, column2) AS max_value
FROM
    SampleTable;

Output:

Output

Explanation: The provided SELECT statement retrieves id, column1, and column2 values from the SampleTable also computing the maximum value between column1 and column2 across all rows, denoted as max_value. The MAX() function perform this calculation, resulting in a single value representing the maximum among corresponding elements of the specified columns.

Example of Finding the Maximum Value Among Multiple Columns in SQLite

Let’s now use the concepts we have learned in this article in a technical example.

First, let’s create the table and insert some data inside it. The following query creates a sales table and inserts three records in it.

Query:

-- create
CREATE TABLE SALES (
  product_name VARCHAR(20),
  jan INT,
  feb INT,
  mar INT
);

-- insert
INSERT INTO SALES VALUES ('Book', 123, 89, 22);
INSERT INTO SALES VALUES ('Pen', 99, 12, 51);
INSERT INTO SALES VALUES ('Sharpner', 82, 47, 90);

The above table contains the information about different products and the number of units sold in January, February, and March. So, the record (‘Book’, 123, 89, 22) states that 123 units, 89 units, and 22 units of the book was sold in January, February, and March respectively.

The following query returns the initial data in the table:

Query:

SELECT * FROM SALES;

Output:

Explanation: Now our table SALES has been created.

Now we will find out the maximum unit of a product sold in three months for all the products in the table. To solve this we will use the MAX() function. As already mentioned, we can use MAX() function to find maximum value from more than 2 values. The following query makes use of MAX() function to find the maximum units sold for each product.

Query:

SELECT
    product_name,
    MAX(jan, feb, mar) AS max_sales
FROM
    SALES;

Output:

Output

Explanation: As we can see that books were sold maximum number of 123 units, pens were sold maximum number of 99 units, and sharpeners were sold maximum number of 90 units.

Conclusion

In this article, we covered how we can find the maximum of multiple columns in SQLite. We had a chance to look at two different methods to go about doing this, first using CASE statement. We understood the CASE statement and how quickly it can get very complicated. We later looked at the MAX() function and understood it MAX() Function. We also how we can use the concepts we learned in this article to a real-life situation through the technical example.