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

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

DB2 OS/390 V8.2. No OLAP functions. I am trying to developed them.
Thank's in advance>
Serge Rielau wrote:
>> I have a problem with following join:
>> First query is generating RANK
>[quoted text clipped - 44 lines]
>>
>> 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
>

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200803/1