WITH test AS (
SELECT 'A' NAME,2 VALUE FROM DUAL UNION ALL
SELECT 'B',1 FROM DUAL UNION ALL
SELECT 'C',4 FROM DUAL UNION ALL
SELECT 'D',3 FROM DUAL
)SELECT C.NAME,C.VALUE,D.VALUE TOVALUE FROM
(SELECT A.*,ROWNUM RN FROM (SELECT T.* FROM TEST T ORDER BY T.VALUE) A) C,
(SELECT B.*,ROWNUM RN FROM (SELECT T.VALUE FROM TEST T ORDER BY T.VALUE DESC) B) D
WHERE C.RN=D.RN
SELECT 'A' name,2 VALUE FROM dual UNION ALL
SELECT 'B', 1 FROM dual UNION ALL
SELECT 'C', 4 FROM dual UNION ALL
SELECT 'D', 3 FROM dual)
SELECT TAB.*, RANK() OVER(ORDER BY VALUE DESC) TOVALUE
FROM TAB
ORDER BY VALUE