Re: helpme~ In oracle9i how to use rank over function

c2061_at_bcline.com (c2061_at_bcline.com) wrote in message news:<45064e2c.0303261122.2f663126_at_posting.google.com>...
> SQL statement :
> select
> deptno,
> ename,
> sal,
> rank() over ( partition by deptno order by sal desc) as rank
> from emp
> where deptno=20
>
> result :
> deptno|ename | sal |rank
> 20 | SCOTT | 3000 |1
> 20 | FORD | 3000 |1
> 20 | JONES | 2975 |3
> 20 | ADAMS | 1100 |4
> 20 | SMITH | 800 |5
>
> so i want to remove duplicate rank value
> how to get result under?
>
> deptno |ename | sal |rank
> 20 |SCOTT | 3000 |1
> 20 |FORD | 3000 |1
> 20 |JONES | 2975 |2
> 20 |ADAMS | 1100 |3
> 20 |SMITH | 800 |4
>
> thank for reading my question..^^

Use dense_rank() instead of rank() Received on Wed Mar 26 2003 - 22:32:04 CET