Summarizing with MIN( ) and MAX( )

       
mysql>
mysql>
mysql>
mysql> CREATE TABLE mail
    -> (
    ->  t               DATETIME,       # when message was sent
    ->  senderUser      CHAR(8),        # sender (source user and host)
    ->  senderHost      CHAR(20),
    ->  recipientUser   CHAR(8),        # recipient (destination user and host)
    ->  recipientHost   CHAR(20),
    ->  size    BIGINT,         # message size in bytes
    ->  INDEX   (t)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO mail (t,senderHost,senderUser,recipientHost,recipientUser,size)
    ->  VALUES
    ->          ('2010-05-11 10:15:08','saturn','barb','mars','tricia',58274),
    ->          ('2010-05-12 12:48:13','mars','tricia','venus','gene',194925),
    ->          ('2010-05-12 15:02:49','mars','phil','saturn','phil',1048),
    ->          ('2010-05-13 13:59:18','saturn','barb','venus','tricia',271),
    ->          ('2010-05-14 09:31:37','venus','gene','mars','barb',2291),
    ->          ('2010-05-14 11:52:17','mars','phil','saturn','tricia',5781),
    ->          ('2010-05-14 14:42:21','venus','barb','venus','barb',98151),
    ->          ('2010-05-14 17:03:01','saturn','tricia','venus','phil',2394482),
    ->          ('2010-05-15 07:17:48','mars','gene','saturn','gene',3824),
    ->          ('2010-05-15 08:50:57','venus','phil','venus','phil',978),
    ->          ('2010-05-15 10:25:52','mars','gene','saturn','tricia',998532),
    ->          ('2010-05-15 17:35:31','saturn','gene','mars','gene',3856),
    ->          ('2010-05-16 09:00:28','venus','gene','mars','barb',613),
    ->          ('2010-05-16 23:04:19','venus','phil','venus','barb',10294),
    ->          ('2010-05-17 12:49:23','mars','phil','saturn','tricia',873),
    ->          ('2010-05-19 22:21:51','saturn','gene','venus','gene',23992)
    -> ;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> SELECT
    -> MIN(t) AS earliest, MAX(t) AS latest,
    -> MIN(size) AS smallest, MAX(size) AS largest
    -> FROM mail;
+---------------------+---------------------+----------+---------+
| earliest            | latest              | smallest | largest |
+---------------------+---------------------+----------+---------+
| 2010-05-11 10:15:08 | 2010-05-19 22:21:51 |      271 | 2394482 |
+---------------------+---------------------+----------+---------+
1 row in set (0.00 sec)

mysql>
mysql> drop table mail;
Query OK, 0 rows affected (0.00 sec)

Related examples in the same category

1.	Calculate the maximum value by using the MAX() function: MAX()
2.	Get the maximum price and minimum price
3.	What are the first and last state names, lexically speaking?
4.	Use the CONCAT( ) expression with MAX( ) to find the value with the largest population part:
5.	Finding Values Associated with Minimum and Maximum Values
6.	Determine the population range:
7.	To obtain the state name associated with the maximum population
8.	Returning the Maximum Value with MAX()
9.	Finds the size of the largest message sent between each pair of sender and recipient values listed

Summarizing with MIN( ) and MAX( ) : MAX « Aggregate Functions « SQL / MySQL

Related examples in the same category