조건이 틀렸네요.
- 원하는 조건 : 6보다 작은 것 중에 최고
- 작성된 조건 : 최고가 6보다 작은 것
SELECT GREATEST( CASE WHEN a < 5 THEN a ELSE 0 END , CASE WHEN b < 5 THEN b ELSE 0 END , CASE WHEN c < 5 THEN c ELSE 0 END , CASE WHEN d < 5 THEN d ELSE 0 END , CASE WHEN e < 5 THEN e ELSE 0 END ) v FROM (SELECT 1 a, 2 b, 3 c, 4 d, 5 e FROM dual) a ;
SELECT MAX(v) v FROM (SELECT CASE lv WHEN 1 THEN a WHEN 2 THEN b WHEN 3 THEN c WHEN 4 THEN d WHEN 5 THEN e END v FROM (SELECT 1 a, 2 b, 3 c, 4 d, 5 e FROM dual) a CROSS JOIN (SELECT 1 lv FROM dual UNION ALL SELECT 2 FROM dual UNION ALL SELECT 3 FROM dual UNION ALL SELECT 4 FROM dual UNION ALL SELECT 5 FROM dual ) copy_t ) a WHERE v < 5 ;