Declaration
MIN([ DISTINCT | ALL ] expr) OVER (analytic_clause)
Description
Returns the minimum value in the specified data.
The MIN() function can accept a string parameter, in which case it returns the smallest string value.
The
DISTINCTkeyword indicates to find the minimum value of distinctexprvalues. However, the result is the same as ifDISTINCTwere omitted.The
ALLkeyword indicates to find all values ofexpr. 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