How Oracle can remove the maximum and minimum values from multiple field columns

First, two functions are introduced (two connections below):

Oracle least () function

Oracle Greatest () function

Example:

To build a table statement:

CREATE TABLE EMP  
(  
  EMPNO number    (4) Not NULL,  
  ename    VARCHAR2 (Ten),  
  JOB      VARCHAR2 (9),  
  MGR number      (4),  
  hiredate DATE,  
  SAL number      (7,2),  
  COMM number     (7,2),  
  DEPTNO   Number (2)  
)

If you require empno, mgr,sal the maximum and minimum values in the three columns, the code is as follows:

SELECT EMPNO, Mgr,sal,  
      Greatest (EMPNO, Mgr,sal) max,  
      least (EMPNO, mgr,sal) min from
emp

The results are shown below:

If you want to display only the largest and smallest rows, the code is as follows:

SELECT EMPNO, Mgr,sal,  
      Greatest (EMPNO, Mgr,sal) max,  
      least (EMPNO, mgr,sal) min from
 emp where rownum< 2;

The effect is as follows:

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/