I have a problem with following join:
First query is generating RANK
SELECT EMPNO,BONUS,(SELECT COUNT(*) + 1
FROM EMPLOYEE
WHERE BONUS > T.BONUS) AS RANK
FROM EMPLOYEE T
ORDER BY BONUS DESC;
EMPNO BONUS RANK
------ ----------- -----------
000010 1000.00 1
000110 900.00 2
000020 800.00 3
000030 800.00 3
000050 800.00 3
000070 700.00 6
000090 600.00 7
000120 600.00 7
000140 600.00 7
000200 600.00 7
000220 600.00 7
000240 600.00 7
000060 500.00 13
000100 500.00 13
........................................

and a query generating ROW_NUMBER

SELECT EMPNO, ROW# + 1 AS ROW_NUM
FROM EMPLOYEE TB1,
TABLE (SELECT COUNT(*) AS ROW# FROM EMPLOYEE TB2
WHERE TB2.EMPNO < TB1.EMPNO) AS TEMP_TAB;
EMPNO ROW_NUM
------ -----------
000010 1
000020 2
000030 3
000050 4
000060 5
000070 6
000090 7
000100 8
000110 9
000120 10
000130 11
000140 12
........................

Please help to constract a join to generate Bonus,Row_number and Rank

Thank's in advance. Leny G.

--
Message posted via http://www.dbmonster.com