[Oracle] 최대값 최소값 구하기(GREATEST/LEAST) 사용법과 예제

MIN, MAX는 여러 Row 중 해달 컬럼의 최대값과 최소값을 구하는 함수라면 GREATEST, LEAST 여러 Column 또는 표현 중에 최대값 최소값을 구하는 함수입니다.

최대값 구하기

(구문)

GREATEST(expr1,expr2....)

(예제)

SELECT GREATEST(1, 2, 3, 4, 5) FROM DUAL;

결과 : 5

 

GREATEST 함수를 이용하여 4번의 수학 시험 중 가장 높은 점수를 구하겠습니다.

(WITH 절을 이용하여 임시테이블 생성)

   WITH MATH_EXAM_SCORE AS (
   
        SELECT '김아무개' NAME, 80 EXAM_1_SCORE, 88 EXAM_2_SCORE, 95 EXAM_3_SCORE, 82 EXAM_4_SCORE FROM DUAL
        UNION ALL
        SELECT '정아무개' NAME, 50 EXAM_1_SCORE, 87 EXAM_2_SCORE, 100 EXAM_3_SCORE, 88 EXAM_4_SCORE FROM DUAL
        UNION ALL
        SELECT '박아무개' NAME, 78 EXAM_1_SCORE, 95 EXAM_2_SCORE, 89 EXAM_3_SCORE, 89 EXAM_4_SCORE FROM DUAL
        UNION ALL
        SELECT '이아무개' NAME, 66 EXAM_1_SCORE, 67 EXAM_2_SCORE, 78 EXAM_3_SCORE, 93 EXAM_4_SCORE FROM DUAL
        UNION ALL
        SELECT '최아무개' NAME, 91 EXAM_1_SCORE, 77 EXAM_2_SCORE, 64 EXAM_32_SCORE, 100 EXAM_4_SCORE FROM DUAL 
   
   ) SELECT NAME, 
    	    GREATEST(EXAM_1_SCORE,EXAM_2_SCORE,EXAM_3_SCORE,EXAM_4_SCORE) MAX_SCORE 
       FROM MATH_EXAM_SCORE

 

최소값 구하기

(구문)

LEAST(expr1,expr2....)

(예제)

SELECT LEAST(1, 2, 3, 4, 5) FROM DUAL;

결과 : 1

 

LEAST 함수를 이용하여 4번의 수학 시험 중 가장 낮은 점수를 구하겠습니다.

(WITH 절을 이용하여 임시테이블 생성)

 WITH MATH_EXAM_SCORE AS (
 
        SELECT '김아무개' NAME, 80 EXAM_1_SCORE, 88 EXAM_2_SCORE, 95 EXAM_3_SCORE, 82 EXAM_4_SCORE FROM DUAL
        UNION ALL
        SELECT '정아무개' NAME, 50 EXAM_1_SCORE, 87 EXAM_2_SCORE, 100 EXAM_3_SCORE, 88 EXAM_4_SCORE FROM DUAL
        UNION ALL
        SELECT '박아무개' NAME, 78 EXAM_1_SCORE, 95 EXAM_2_SCORE, 89 EXAM_3_SCORE, 89 EXAM_4_SCORE FROM DUAL
        UNION ALL
        SELECT '이아무개' NAME, 66 EXAM_1_SCORE, 67 EXAM_2_SCORE, 78 EXAM_3_SCORE, 93 EXAM_4_SCORE FROM DUAL
        UNION ALL
        SELECT '최아무개' NAME, 91 EXAM_1_SCORE, 77 EXAM_2_SCORE, 64 EXAM_32_SCORE, 100 EXAM_4_SCORE FROM DUAL 
   
   )SELECT NAME, 
   	   LEAST(EXAM_1_SCORE,EXAM_2_SCORE,EXAM_3_SCORE,EXAM_4_SCORE) MAX_SCORE 
      FROM MATH_EXAM_SCORE

※ NULL이 포함된 경우

- GREATEST, LEAST 모두 NULL반환

​

※ 숫자가 아닌 다른 데이터 타입이 포함된 경우

- 오라클 오류 발생

  ORA-01722: 수치가 부적합합니다

  01722. 00000 - "invalid number"

  *Cause: The specified number was invalid.

  *Action: Specify a valid number.