How SQLite Max() Works

The SQLite max() function returns the maximum value from all values in a group.

The maximum value is the value that would appear last in a query that uses an ORDER BY clause on the same column.

Example

Here’s an example to demonstrate.

SELECT max(Price) FROM Products;

Result:

389.45

Here’s a query that returns all rows in that table.

SELECT * FROM Products 
ORDER BY Price;

Result:

ProductId   ProductName  Price     
----------  -----------  ----------
5           Red Widget             
3           Widget Open  89.27     
4           Foobar Set   120.0     
2           Widget Hold  139.5     
1           Blue Widget  389.45

Notice that the first row has a NULL value for the price. The max() function ignores that, because the other rows have non-NULL values. If all rows were NULL, then max() would return NULL.

I used the ORDER BY clause in this example. As mentioned, the max() function returns the value that appears last when using this clause (assuming you order the column in ascending order, which is the default).

Just to be clear, I could have used the following for the same result:

SELECT * FROM Products 
ORDER BY Price ASC;

Combine Max() with Count()

Here’s an example that combines the max() function with the count() function to return the maximum count.

SELECT max(AlbumCount) 
FROM (SELECT ArtistId, count(Title) AS AlbumCount
FROM Album
GROUP BY ArtistId);

Result:

We can verify this by running another query with an ORDER BY clause.

SELECT 
  ar.Name, 
  count(al.Title) AS AlbumCount
FROM Album al
INNER JOIN Artist ar
ON ar.ArtistId = al.ArtistId
GROUP BY ar.Name
ORDER BY AlbumCount DESC
LIMIT 10;

Result:

Name                  AlbumCount
--------------------  ----------
Iron Maiden           21        
Led Zeppelin          14        
Deep Purple           11        
Metallica             10        
U2                    10        
Ozzy Osbourne         6         
Pearl Jam             5         
Faith No More         4         
Foo Fighters          4         
Lost                  4

Dealing with NULL Values

As mentioned, if there are no non-NULL values in the group (i.e. all values are NULL), then max() returns NULL.

SELECT 
  max(123),
  max(NULL);

Result:

max(123)    max(NULL) 
----------  ----------
123

Using Max() on Strings

As mentioned, max() returns the value that would be returned last using the ORDER BY clause (assuming it’s using the default ascending order).

This same concept applies to strings.

SELECT max(ProductName) FROM Products;

Result:

Widget Opener

So it doesn’t return the string with the maximum amount of characters or anything like that.

We can verify this result with a query using the ORDER BY clause.

SELECT * FROM Products 
ORDER BY ProductName;

Result:

ProductId   ProductName            Price     
----------  ---------------------  ----------
1           Blue Widgets (6 Pack)  389.45    
4           Foobar Set             120.0     
5           Red Widget                       
2           Widget Holder          139.5     
3           Widget Opener          89.27