SQLite min() function

Description

The min() aggregate function returns the minimum non-NULL value of all values in the group. The minimum value is the first non-NULL value that would appear in an ORDER BY of the column. The function returns NULL if and only if there are no non-NULL values in the group.

Syntax :

min(column);

Where expr is an expression.

Example: SQLite min() function

The following statement will return the minimum 'book_price' from 'book_mast' table.

Sample table: book_mast

Sample Output:

sqlite> SELECT MIN(book_price)
  ...> FROM book_mast;
  MIN(book_price)
  ---------------
  45  

Example: SQLite min() function with group by

SQLite min() function retrieves the minimum value of an expression which has undergone a grouping operation if used with GROUP BY clause. The following SQLite statement will extract all "cate_id"s and the minimum 'book_price' for each group of 'cate_id'. GROUP BY clause has grouped "cate_id"s.

Sample table: book_mast

Sample Output:

sqlite> SELECT cate_id, MIN( book_price)
  ...> FROM book_mast
  ...> GROUP BY cate_id;
  cate_id     MIN( book_price)
  ----------  ----------------
  CA001       85
  CA002       105.5
  CA003       45
  CA004       88
  CA005       50.5  

Pictorial Presentation

Example: SQLite min() function with group by and order by

The following SQLite statement will extract those countries ('country') and publisher cities ('pub_city') which have the minimum number of branches ('no_of_branch') for each group of 'country' and 'pub_city'. 'GROUP BY' clause have grouped 'country' and 'pub_city' and the 'country' column have been sorted in ascending order by the usage of ORDER BY clause.

Sample table: publisher

Sample Output:

sqlite> SELECT country,pub_city,MIN(no_of_branch)
   ...> FROM publisher
   ...> GROUP BY country,pub_city;
   country     pub_city    MIN(no_of_branch)
   ----------  ----------  -----------------
   Australia   Adelaide    6
   India       Mumbai      10
   India       New Delhi   10
   UK          Cambridge   6
   UK          London      8
   USA         Houstan     25
   USA         New York    10

Example: SQLite min() function with distinct

SQLite min() function retrieves the unique minimum value of an expression if the function is accompanied by a DISTINCT clause. The following statement will extract category 'cat_id' wise minimum number of page 'no_page' from the 'book_mast' table.

Sample table: book_mast

Sample Output:

sqlite> SELECT cate_id,MIN(DISTINCT no_page)
  ...> FROM book_mast
  ...> GROUP BY cate_id;
  cate_id     MIN(DISTINCT no_page)
  ----------  ---------------------
  CA001       201
  CA002       300
  CA003       95
  CA004       165
  CA005       88

Previous: Max()
Next: Sum()