How to Select Row With Max Value in in SQLite

In SQLite, retrieving rows with the maximum value for a specific column, grouped by another column's distinct values can be a challenging task. Whether you're analyzing data trends or identifying top performers, this operation can provide valuable output. In this beginner-friendly guide, we will explore two common methods to fetch such rows using SQLite.

How to Select Row With Max Value in in SQLite?

MAX() Function is used to return the maximum value of a set of values. It can be used with a single column or with multiple columns. To get the maximum value for each column in a table, we can use the MAX() function along with below methods which are given below:

1. Using SELF-JOIN
   
2. USING WHERE CLAUSE

let's Setting Up the Environment

For better understanding, we need a table on which we will perform various operations and queries. The below query creates a table and then inserts some records in it.

Query:

CREATE TABLE test (
  id INTEGER PRIMARY KEY,
  val1 VARCHAR(20),
  val2 INTEGER
);

INSERT INTO test VALUES (21, 'val1', 32);
INSERT INTO test VALUES (11, 'val2', 90);
INSERT INTO test VALUES (90, 'val1', 18);
INSERT INTO test VALUES (77, 'val1', 65);
INSERT INTO test VALUES (43, 'val3', 20);
INSERT INTO test VALUES (81, 'val3', 88);
INSERT INTO test VALUES (29, 'val2', 72);
INSERT INTO test VALUES (55, 'val2', 47);
INSERT INTO test VALUES (72, 'val3', 11);

Output:

1. Using SELF JOIN

A self-join happens when a table is joined with itself. To solve the problem presented to us we will join the original table with a little modified version of the table. In the modified version we will group the table by val1 column and then use the MAX() function to find the maximum value of val2 for each group. We will later join this with the original table on the values of t1.val1=t2.val1 and t1.val2=t2.max_val2.

Query:

SELECT t1.id, t1.val1, t1.val2 FROM test t1
JOIN (
  SELECT val1, MAX(val2) AS max_val2
  FROM test
  GROUP BY val1
) t2
ON t1.val1=t2.val1 AND t1.val2=t2.max_val2;

Output:

Explanation: In the above query we have retrieves rows from the test table where the combination of val1 and val2 is the maximum for each distinct value in the val1 column. It does this by joining the test table with a subquery that calculates the maximum val2 value for each val1 group. The ON clause ensures that the rows are matched based on both val1 and the maximum val2 value.

2. USING WHERE CLAUSE

We will use a WHERE clause with a subquery to find the max value. similar in method 1, we will create a modified version of the table in which we group the data and find the maximum value for each group. Just the difference in this method is that rather than using JOIN, Here we will use WHERE to compare the value in the original table with the modified table.

Query:

SELECT t1.id, t1.val1, t1.val2 FROM test t1, (
  SELECT val1, MAX(val2) AS max_val2
  FROM test
  GROUP BY val1
) t2
WHERE t1.val1=t2.val1 AND t1.val2=t2.max_val2;

Output:

Explanation: In the above query we have retrieves rows from the test table where the value in the val2 column is the maximum for each distinct value in the val1 column. It uses a subquery to find the maximum value for each val1 group and then joins the original table with this subquery to filter the rows accordingly.

Conclusion

In this article, we have covered how we can find the records which have the maximum value for a column for each distinct value of another column in SQLite. We had a chance to look at two different methods to go about doing this, first using SELF-JOIN and later looked at how we can achieve the same using WHERE clause. We also how we can use the concepts we learned in this article to a real-life situation through the technical example.