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:
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:
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:
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.
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.