lenygold via DBMonster.com wrote:
> 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
Which version of DB2 and which platform?
Note that DB2 for LUW supports OLAP functions for all supported
versions. There is no need for joins

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab