The SQLite min() function returns the minimum non-NULL value from all values in a group.

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

Example

Here’s an example to demonstrate.

SELECT min(Price) FROM Products;

Result:

89.27

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 min() function ignores that and uses the minimum non-NULL value.

Combine Min() with Count()

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

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

Result:

1       

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 ASC
LIMIT 3;

Result:

Name                            AlbumCount
------------------------------  ----------
Aaron Copland & London Symphon  1         
Aaron Goldberg                  1         
Academy of St. Martin in the F  1         

Combine with Max() and Avg()

Here’s another example where I add the max() and avg() functions to the mix.

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

Result:

min(AlbumCount)  max(AlbumCount)  avg(AlbumCount) 
---------------  ---------------  ----------------
1                21               1.70098039215686

Using Min() on Strings

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

This same concept applies to strings.

SELECT min(ProductName) FROM Products;

Result:

Blue Widgets (6 Pack)

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