SQLite min function

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

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

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
sqlite> SELECT MIN(book_price)
  ...> FROM book_mast;
  MIN(book_price)
  ---------------
  100  

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

sqlite> SELECT cate_id, MIN( book_price)
  ...> FROM book_mast
  ...> GROUP BY cate_id;
  cate_id     MIN( book_price)
  ----------  ----------------
  CA001       112
  CA002       105.5
  CA003       200
  CA004       100
  CA005       135  

Pictorial Presentation

mysql aggregate functions and grouping min functiongroup by 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

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
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

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
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       165
  CA004       165
  CA005       225