Syntax
MIN([ DISTINCT | ALL ] expr) OVER (analytic_clause)
Purpose
Returns the minimum value in the specified data.
The MIN() function can take a string parameter, returning the smallest string value.
The
DISTINCTkeyword specifies to find the minimum value of distinctexprvalues, but the result is the same as omittingDISTINCT.The
ALLkeyword specifies to find the minimum value of allexprvalues. The default isALL.
Examples
obclient> CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected
obclient> INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected
obclient> INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected
obclient> SELECT LAST_NAME, MIN(SALARY) OVER(PARTITION BY JOB_ID) MIN_S FROM EXPLOYEES;
+-----------+-------+
| LAST_NAME | MIN_S |
+-----------+-------+
| JIM | 2000 |
| MIKE | 11000 |
| LILY | 11000 |
| TOM | 11000 |
+-----------+-------+
4 rows in set