-- PORTFOLOIO ¤ WHITE PAPERS ¤ HOME PAGE drop table t; Table dropped -- Using the sign function to make decode determine -- greater than, less than, greater than or equal to, less than or equal to. -- -- I found this on the asktom.oracle.com website. -- I have used it so many times since I found it that I just want to keep a record of it here. create table t (x number); Table created insert into t (x) values(0); 1 row inserted insert into t (x) values (1); 1 row inserted insert into t (x) values (2); 1 row inserted insert into t (x) values (3); 1 row inserted insert into t (x) values (4); 1 row inserted insert into t (x) values (5); 1 row inserted commit; Commit complete select * from t X -- 0 1 2 3 4 5 6 rows selected / select x, decode( sign(x-1), -1, 'Less than 1', 'Greater than or equal to 1' ) c1, decode( sign(x-2), -1, 'Less than 2', decode( sign(x-4), 1, 'Greater than 4', 'Between 2 and 4' ) ) c2, decode( sign(x-4), 1, 'Greater than 4', 'Less than 4' ) c3 from t order by x asc X C1 C2 C3 -- -------------------------- --------------- -------------- 0 Less than 1 Less than 2 Less than 4 1 Greater than or equal to 1 Less than 2 Less than 4 2 Greater than or equal to 1 Between 2 and 4 Less than 4 3 Greater than or equal to 1 Between 2 and 4 Less than 4 4 Greater than or equal to 1 Between 2 and 4 Less than 4 5 Greater than or equal to 1 Greater than 4 Greater than 4 6 rows selected / select x, decode( sign(x-1), -1, 'Less than 1', null ) c1, decode( sign(x-2), -1, null, decode( sign(x-4), 1, null, 'Between 2 and 4' ) ) c2, decode( sign(x-4), 1, 'Greater than 4', null ) c3 from t order by x asc X C1 C2 C3 -- ----------- --------------- -------------- 0 Less than 1 1 2 Between 2 and 4 3 Between 2 and 4 4 Between 2 and 4 5 Greater than 4 6 rows selected /