SQLite max() function

Description

The max() aggregate function returns the maximum value of all values in the group. The maximum value is the value that would be returned last in an ORDER BY on the same column. The function returns NULL if and only if there are no non-NULL values in the group.

Syntax:

max(expr);

Where expr is an expression.

Example: SQLite max() function

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

Sample table: book_mast

Sample Output:

sqlite> SELECT MAX(book_price)
...> FROM book_mast;
MAX(book_price)
---------------
250

Example: SQLite max() function with GROUP BY, HAVING

SQLite MAX() function retrieves the maximum value from an expression which has undergone a grouping operation by GROUP BY clause and filtered using HAVING clause followed by some condition. The following SQLite statement will extract those countries ('country') which have eight or more branches.

Sample table: publisher

Sample Output:

sqlite> SELECT country,MAX(no_of_branch)
  ...> FROM publisher
  ...> GROUP BY country
  ...> HAVING MAX(no_of_branch)>=8;
  country     MAX(no_of_branch)
 ----------  -----------------
 India       10
 UK          8
 USA         25

Example: SQLite max() function with GROUP BY

SQLite MAX() function with GROUP BY retrieves maximum value of an expression which has undergone a grouping operation (usually based upon one column or a list of comma separated columns). The following SQLite statement will extract all “cate_id”s and the maximum 'book_price' in each group of 'cate_id'. ‘GROUP BY ‘ clause have grouped “cate_id's”.

Sample table: book_mast

Sample Output:

sqlite> SELECT cate_id, MAX( book_price)
  ...> FROM book_mast
  ...> GROUP BY cate_id;
  cate_id     MAX( book_price)
----------  ----------------
CA001       145
CA002       250
CA003       200
CA004       100
CA005       180

Pictorial Presentation

SQLite MAX() function with group by on two columns

The following SQLite statement will extract those countries ('country') and publisher cities ('pub_city') which has the maximum number of branches ('no_of_branch') in each group of 'country' and 'pub_city'.

Sample table: publisher

Sample Output:

sqlite> SELECT country,pub_city,MAX(no_of_branch)
 ...> FROM publisher
 ...> GROUP BY country,pub_city;
 country     pub_city    MAX(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    15
 

SQLite MAX with group by and order by

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

Sample Output:

sqlite> SELECT country,pub_city,MAX(no_of_branch)
 ...> FROM publisher
 ...> GROUP BY country,pub_city
 ...> ORDER BY country;
 country     pub_city    MAX(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    15
 

SQLite MAX() function with distinct

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

Sample table : book_mast

Sample Output:

sqlite> SELECT cate_id,MAX(DISTINCT no_page)
  ...> FROM book_mast
  ...> GROUP BY cate_id;
  cate_id     MAX(DISTINCT no_page)
  ----------  ---------------------
  CA001       345
  CA002       600
  CA003       510
  CA004       350
  CA005       350
  

Previous: Group_Concat()
Next: Min()