| urgent [message #39792] |
Mon, 12 August 2002 04:26  |
Victoria
Messages: 152
Registered: July 2002
|
Senior Member |
|
|
Hi,
I have two columns ina table with the values like this.
DE AMOUNT
-- ---------
10 2000
10 2000
10 2500
10 3000
20 2000
20 1000
20 200
30 7000
30 9000
30 8000
40 2000
I need the output like
DE AMOUNT order
-- -----------------
10 2000 3
10 2000 3
10 2500 2
10 3000 1
20 2000 1
20 1000 2
20 200 3
30 7000 3
30 9000 1
30 8000 2
40 2000 1
But here i should not use rank function.Can anyone suggest me a way out????
Tx in advance
Victoria
|
|
|
|
| Re: urgent [message #39793 is a reply to message #39792] |
Mon, 12 August 2002 04:32  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Use the RANK() or DENSE_RANK() function:
select deptno, sal, rank() over ( partition by deptno
order by sal desc) ranked_value from emp;
DEPTNO SAL RANKED_VALUE
---------- ---------- ------------
10 5000 1
10 2450 2
10 1300 3
20 3000 1
20 3000 1
20 2975 3
20 1100 4
20 800 5
30 2850 1
30 1600 2
30 1500 3
DEPTNO SAL RANKED_VALUE
---------- ---------- ------------
30 1250 4
30 1250 4
30 950 6
14 rows selected.
RANK will, in case of a tie, skip a rank. DENSE_RANK() will continue the ranking and will disregard ties.
HTh,
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
