Syntax
MIN([ DISTINCT | ALL ] expr) OVER (analytic_clause)
Purpose
MIN() returns the minimum value in the specified data set.
The argument of MIN() can be a string, in which case the minimum string value is returned.
The
DISTINCTkeyword can be used to retrieve the minimum of distinct values ofexpr. However, the result is the same as omittingDISTINCT.The
ALLkeyword indicates to search all values ofexpr. By default,ALLis used.
Examples
obclient> CREATE TABLE employees(last_name CHAR(10), salary DECIMAL, job_id CHAR(32));
Query OK, 0 rows affected
obclient> INSERT INTO employees VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected
obclient> INSERT INTO employees VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO employees VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO employees VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected
obclient> SELECT last_name, MIN(salary) OVER(PARTITION BY job_id) min_s FROM employees;
+-----------+-------+
| last_name | min_s |
+-----------+-------+
| JIM | 2000 |
| MIKE | 11000 |
| LILY | 11000 |
| TOM | 11000 |
+-----------+-------+
4 rows in set