SELECT a.*, (select count(*) + 1 from test_rank b where a.point < b.point) rk_all,
(select count(*) + 1 from test_rank b where a.deptno= b.deptno and a.point < b.point) rk_dept
from test_rank a
;

아! 안타깝게도 8.0 버전에서는 스칼라서브쿼리가 안됩니다.

아. 이런. 8버전이 없어서 테스트가 잘안되서. 다시 해야겠네요.

select a.empno, a.deptno, a.point, b.rnum, d.rnum - c.dnum + 1 drnum
from test_rank a,
(
select min(rownum) rnum, point
from
(
select empno, deptno, point
from test_rank
order by point desc
)
group by point
) b,
(
select min(rownum) dnum, deptno
from test_rank
group by deptno
order by deptno
) c,
(
select min(rownum) rnum, deptno, point
from
(
select empno, deptno, point
from test_rank
order by deptno asc, point desc
)
group by deptno, point
) d
where a.point = b.point
and a.deptno = c.deptno
and a.deptno = d.deptno
and a.point = d.point
order by a.deptno, d.rnum - c.dnum + 1, a.empno;

아! 8.0에선 안되는게 너무 많네요...
인라인뷰 안에서는 order by를 사용할 수 없습니다.

재밋게 풀어봤는데 틀렸네요. 아쉽습니다. 그리고 ... 감사합니다.

이것은 되려나요?
select x.*, y.rk_all, z.rk_dept
from
test_rank x
,
(
SELECT a.empno,count(b.empno)+1 rk_all
from test_rank a, test_rank b
where a.point < b.point(+)
group by a.empno, a.point ) y
,
(
SELECT a.deptno,a.empno, a.point, count(b.empno)+1 rk_dept
from test_rank a, test_rank b
where a.point < b.point(+) and a.deptno = b.deptno(+)
group by a.deptno,a.empno, a.point) z
where
x.empno = y.empno
and x.empno = z.empno
order by x.empno, x.deptno
;

잘 하셨습니다.
x, y, z 3개의 집합으로 나누어 다시 조인하셨는데요.
각 집합에는 공통 분모가 존재합니다.
굳이 3개로 나누지 않고 한번에 될 수 있습니다.
y 집합만 가지고 잘만 응용하시면 정답에 근접할 수 있을것 같네요.

답나오기 바뻐서 필요없는 부분있는걸 몰랐네요. 감사합니다.

select y.empno, z.deptno, z.point, y.rk_all, z.rk_dept
from
(
SELECT a.empno,count(b.empno)+1 rk_all
from test_rank a, test_rank b
where a.point < b.point(+)
group by a.empno, a.point ) y
,
(
SELECT a.deptno,a.empno, a.point, count(b.empno)+1 rk_dept
from test_rank a, test_rank b
where a.point < b.point(+) and a.deptno = b.deptno(+)
group by a.deptno,a.empno, a.point) z
where
y.empno = z.empno
order by y.empno, z.deptno
;

WITH TEST_RANK
AS
(
SELECT 1 EMPNO, 10 DEPTNO, 100 POINT FROM DUAL
UNION ALL SELECT 2, 10, 90 FROM DUAL
UNION ALL SELECT 3, 10, 80 FROM DUAL
UNION ALL SELECT 4, 20, 100 FROM DUAL
UNION ALL SELECT 5, 20, 90 FROM DUAL
UNION ALL SELECT 6, 20, 80 FROM DUAL
UNION ALL SELECT 7, 30, 95 FROM DUAL
UNION ALL SELECT 8, 30, 85 FROM DUAL
UNION ALL SELECT 9, 30, 95 FROM DUAL
)
SELECT B.EMPNO,
B.DEPTNO,
B.POINT,
COUNT(DECODE(A.POINT,B.POINT,DECODE(SIGN(A.EMPNO-B.EMPNO),-1,1,0,1),1)) ROW_NB,
COUNT(DISTINCT A.POINT) DENSE_RN,
COUNT(DECODE(A.POINT,B.POINT,NULL,1))+1 RANK,
COUNT(DECODE(A.POINT,B.POINT,NULL,DECODE(A.DEPTNO,B.DEPTNO,1)))+1 RANK_DEPT
FROM TEST_RANK A, TEST_RANK B
WHERE A.POINT >= B.POINT
GROUP BY B.EMPNO, B.DEPTNO, B.POINT
ORDER BY B.DEPTNO, RANK_DEPT;

ROW_NB : ROW_NUMBER
DENSE_RN : DENSE_RANK
RANK : RANK

입니다~

위에것 비교하시면서 보기 편하도록.. ^^;

WITH TEST_RANK
AS
(
SELECT 1 EMPNO, 10 DEPTNO, 100 POINT FROM DUAL
UNION ALL SELECT 2, 10, 90 FROM DUAL
UNION ALL SELECT 3, 10, 80 FROM DUAL
UNION ALL SELECT 4, 20, 100 FROM DUAL
UNION ALL SELECT 5, 20, 90 FROM DUAL
UNION ALL SELECT 6, 20, 80 FROM DUAL
UNION ALL SELECT 7, 30, 95 FROM DUAL
UNION ALL SELECT 8, 30, 85 FROM DUAL
UNION ALL SELECT 9, 30, 95 FROM DUAL
)
SELECT A.EMPNO,
A.DEPTNO,
A.POINT,
ROW_NUMBER() OVER (ORDER BY A.POINT DESC, A.EMPNO ASC) RN,
CNT RN1,
DENSE_RANK() OVER (ORDER BY A.POINT DESC) DENSE_RK,
CNT3 DENSE_RK1,
RANK() OVER (ORDER BY A.POINT DESC) RK,
B.CNT1+1 RN1,
RANK() OVER(PARTITION BY DEPTNO ORDER BY POINT DESC) RK_DEPT,
B.CNT2+1 RK_DEPT1
FROM TEST_RANK A, (SELECT B.EMPNO,
COUNT(DECODE(A.POINT,B.POINT,DECODE(SIGN(A.EMPNO-B.EMPNO),-1,1,0,1),1)) CNT,
COUNT(DISTINCT A.POINT) CNT3,
COUNT(DECODE(A.POINT,B.POINT,NULL,1)) CNT1,
COUNT(DECODE(A.POINT,B.POINT,NULL,DECODE(A.DEPTNO,B.DEPTNO,1))) CNT2
FROM TEST_RANK A, TEST_RANK B
WHERE A.POINT >= B.POINT
GROUP BY B.EMPNO
) B
WHERE A.EMPNO = B.EMPNO
ORDER BY A.DEPTNO, RN, EMPNO;

마농님 대단하시다. 발상자체가 다르네