我现在有下面这样的数据
name value
A 2
B 1
C 4
D 3
。。。
能通过查询得到下面的结果集么?
name value tovalue
B 1 4
A 2 3
D 3 2
C 4 1
。。
也就是说 先按照value列排序 然后将结果后增加对应结果集的倒序值列
这样的SQL怎么写?求大神给个思路
谢谢
------解决方案--------------------
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