SQLite max function

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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

book_id book_name isbn_no cate_id aut_id pub_id dt_of_pub pub_lang no_page book_price
BK001 Introduction to Electrodynamics 0000979001 CA001 AUT001 P003 2001-05-08 English 201 85.00
BK002 Understanding of Steel Construction 0000979002 CA002 AUT002 P001 2003-07-15 English 300 105.50
BK003 Guide to Networking 0000979003 CA003 AUT003 P002 2002-09-10 Hindi 510 200.00
BK004 Transfer of Heat and Mass 0000979004 CA002 AUT004 P004 2004-02-16 English 600 250.00
BK005 Conceptual Physics 0000979005 CA001 AUT005 P006 2003-07-16   345 145.00
BK006 Fundamentals of Heat 0000979006 CA001 AUT006 P005 2003-08-10 German 247 112.00
BK007 Advanced 3d Graphics 0000979007 CA003 AUT007 P002 2004-02-16 Hindi 165 56.00
BK008 Human Anatomy 0000979008 CA005 AUT008 P006 2001-05-17 German 88 50.50
BK009 Mental Health Nursing 0000979009 CA005 AUT009 P007 2004-02-10 English 350 145.00
BK010 Fundamentals of Thermodynamics 0000979010 CA002 AUT010 P007 2002-10-14 English 400 225.00
BK011 The Experimental Analysis of Cat 0000979011 CA004 AUT011 P005 2007-06-09 French 225 95.00
BK012 The Nature of World 0000979012 CA004 AUT005 P008 2005-12-20 English 350 88.00
BK013 Environment a Sustainable Future 0000979013 CA004 AUT012 P001 2003-10-27 German 165 100.00
BK014 Concepts in Health 0000979014 CA005 AUT013 P004 2001-08-25   320 180.00
BK015 Anatomy & Physiology 0000979015 CA005 AUT014 P008 2000-10-10 Hindi 225 135.00
BK016 Networks and Telecommunications 00009790_16 CA003 AUT015 P003 2002-01-01 French 95 45.00
  1. sqlite> SELECT MAX(book_price)  
  2. ...> FROM book_mast;  
  3. MAX(book_price)  
  4. ---------------  
  5. 95  

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

pub_id pub_name pub_city country country_office no_of_branch estd
P001 Jex Max Publication New York USA New York 15 1969-12-25
P002 BPP Publication Mumbai India New Delhi 10 1985-10-01
P003 New Harrold Publication Adelaide Australia Sydney 6 1975-09-05
P004 Ultra Press Inc. London UK London 8 1948-07-10
P005 Mountain Publication Houstan USA Sun Diego 25 1975-01-01
P006 Summer Night Publication New York USA Atlanta 10 1990-12-10
P007 Pieterson Grp. of Publishers Cambridge UK London 6 1950-07-15
P008 Novel Publisher Ltd. New Delhi India Bangalore 10 2000-01-01
  1. sqlite> SELECT country,MAX(no_of_branch)  
  2.   ...> FROM publisher
       ...> 
    GROUP BY country  
  3.   ...> HAVING MAX(no_of_branch)>=8;  
  4.   country     MAX(no_of_branch)  
  5.  ----------  -----------------  
  6.  Australia   6  
  7.  India       10  
  8.  UK          8  
  9.  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

book_id book_name isbn_no cate_id aut_id pub_id dt_of_pub pub_lang no_page book_price
BK001 Introduction to Electrodynamics 0000979001 CA001 AUT001 P003 2001-05-08 English 201 85.00
BK002 Understanding of Steel Construction 0000979002 CA002 AUT002 P001 2003-07-15 English 300 105.50
BK003 Guide to Networking 0000979003 CA003 AUT003 P002 2002-09-10 Hindi 510 200.00
BK004 Transfer of Heat and Mass 0000979004 CA002 AUT004 P004 2004-02-16 English 600 250.00
BK005 Conceptual Physics 0000979005 CA001 AUT005 P006 2003-07-16   345 145.00
BK006 Fundamentals of Heat 0000979006 CA001 AUT006 P005 2003-08-10 German 247 112.00
BK007 Advanced 3d Graphics 0000979007 CA003 AUT007 P002 2004-02-16 Hindi 165 56.00
BK008 Human Anatomy 0000979008 CA005 AUT008 P006 2001-05-17 German 88 50.50
BK009 Mental Health Nursing 0000979009 CA005 AUT009 P007 2004-02-10 English 350 145.00
BK010 Fundamentals of Thermodynamics 0000979010 CA002 AUT010 P007 2002-10-14 English 400 225.00
BK011 The Experimental Analysis of Cat 0000979011 CA004 AUT011 P005 2007-06-09 French 225 95.00
BK012 The Nature of World 0000979012 CA004 AUT005 P008 2005-12-20 English 350 88.00
BK013 Environment a Sustainable Future 0000979013 CA004 AUT012 P001 2003-10-27 German 165 100.00
BK014 Concepts in Health 0000979014 CA005 AUT013 P004 2001-08-25   320 180.00
BK015 Anatomy & Physiology 0000979015 CA005 AUT014 P008 2000-10-10 Hindi 225 135.00
BK016 Networks and Telecommunications 00009790_16 CA003 AUT015 P003 2002-01-01 French 95 45.00
  1. sqlite> SELECT cate_id, MAX( book_price)  
  2.   ...> FROM book_mast  
  3.   ...> GROUP BY cate_id;  
  4.   cate_id     MAX( book_price)  
  5. ----------  ----------------  
  6. CA001       85  
  7. CA002       250  
  8. CA003       56  
  9. CA004       95  
  10. CA005       50.5  

Pictorial Presentation

mysql aggregate functions and grouping max function group by 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

pub_id pub_name pub_city country country_office no_of_branch estd
P001 Jex Max Publication New York USA New York 15 1969-12-25
P002 BPP Publication Mumbai India New Delhi 10 1985-10-01
P003 New Harrold Publication Adelaide Australia Sydney 6 1975-09-05
P004 Ultra Press Inc. London UK London 8 1948-07-10
P005 Mountain Publication Houstan USA Sun Diego 25 1975-01-01
P006 Summer Night Publication New York USA Atlanta 10 1990-12-10
P007 Pieterson Grp. of Publishers Cambridge UK London 6 1950-07-15
P008 Novel Publisher Ltd. New Delhi India Bangalore 10 2000-01-01
  1. sqlite> SELECT country,pub_city,MAX(no_of_branch)  
  2.  ...> FROM publisher  
  3.  ...> GROUP BY country,pub_city;  
  4.  country     pub_city    MAX(no_of_branch)  
  5.  ----------  ----------  -----------------  
  6.  Australia   Adelaide    6  
  7.  India       Mumbai      10  
  8.  India       New Delhi   10  
  9.  UK          Cambridge   6  
  10.  UK          London      8  
  11.  USA         Houstan     25  
  12.  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.

  1. sqlite> SELECT country,pub_city,MAX(no_of_branch)  
  2.  ...> FROM publisher  
  3.  ...> GROUP BY country,pub_city  
  4.  ...> ORDER BY country;  
  5.  country     pub_city    MAX(no_of_branch)  
  6.  ----------  ----------  -----------------  
  7.  Australia   Adelaide    6  
  8.  India       Mumbai      10  
  9.  India       New Delhi   10  
  10.  UK          Cambridge   6  
  11.  UK          London      8  
  12.  USA         Houstan     25  
  13.  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

book_id book_name isbn_no cate_id aut_id pub_id dt_of_pub pub_lang no_page book_price
BK001 Introduction to Electrodynamics 0000979001 CA001 AUT001 P003 2001-05-08 English 201 85.00
BK002 Understanding of Steel Construction 0000979002 CA002 AUT002 P001 2003-07-15 English 300 105.50
BK003 Guide to Networking 0000979003 CA003 AUT003 P002 2002-09-10 Hindi 510 200.00
BK004 Transfer of Heat and Mass 0000979004 CA002 AUT004 P004 2004-02-16 English 600 250.00
BK005 Conceptual Physics 0000979005 CA001 AUT005 P006 2003-07-16   345 145.00
BK006 Fundamentals of Heat 0000979006 CA001 AUT006 P005 2003-08-10 German 247 112.00
BK007 Advanced 3d Graphics 0000979007 CA003 AUT007 P002 2004-02-16 Hindi 165 56.00
BK008 Human Anatomy 0000979008 CA005 AUT008 P006 2001-05-17 German 88 50.50
BK009 Mental Health Nursing 0000979009 CA005 AUT009 P007 2004-02-10 English 350 145.00
BK010 Fundamentals of Thermodynamics 0000979010 CA002 AUT010 P007 2002-10-14 English 400 225.00
BK011 The Experimental Analysis of Cat 0000979011 CA004 AUT011 P005 2007-06-09 French 225 95.00
BK012 The Nature of World 0000979012 CA004 AUT005 P008 2005-12-20 English 350 88.00
BK013 Environment a Sustainable Future 0000979013 CA004 AUT012 P001 2003-10-27 German 165 100.00
BK014 Concepts in Health 0000979014 CA005 AUT013 P004 2001-08-25   320 180.00
BK015 Anatomy & Physiology 0000979015 CA005 AUT014 P008 2000-10-10 Hindi 225 135.00
BK016 Networks and Telecommunications 00009790_16 CA003 AUT015 P003 2002-01-01 French 95 45.00
  1. sqlite> SELECT cate_id,MAX(DISTINCT no_page)  
  2.   ...> FROM book_mast  
  3.   ...> GROUP BY cate_id;  
  4.   cate_id     MAX(DISTINCT no_page)  
  5.   ----------  ---------------------  
  6.   CA001       345  
  7.   CA002       600  
  8.   CA003       95  
  9.   CA004       350  
  10.   CA005       88  
  11.