Home » SQL & PL/SQL » SQL & PL/SQL » Rank (Oracle, 10g, Windows Server 2003)
Rank [message #447523] Mon, 15 March 2010 12:44 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,

I ran the following query

select deptno,sal,
rank() over(partition by deptno order by sal) rk
from emp
Where deptno = 10;

DEPTNO    SAL    RK
10        1300   1
10        2450   2
10        5000   3



My requirement is that DEPTNO i.e. 10 should be printed only once


DEPTNO    SAL    RK
10        1300   1
          2450   2
          5000   3


Regards,
Ritesh
Re: Rank [message #447524 is a reply to message #447523] Mon, 15 March 2010 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2 ways:

1/ break on deptno nodup

2/ select decode(rank...,1,deptno) deptno, ...

Regards
Michel
Re: Rank [message #447525 is a reply to message #447524] Mon, 15 March 2010 13:08 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Michel,
Thanks

I got the 2nd method

select decode(rank() over(partition by deptno order by sal),1,deptno) rk,sal
from emp
where deptno = 10;


RK   SAL
10   1300
     2450
     50


Please tell me the first one


Regards,
Ritesh

[Updated on: Mon, 15 March 2010 13:09]

Report message to a moderator

Re: Rank [message #447526 is a reply to message #447525] Mon, 15 March 2010 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a SQL*Plus method:
SQL> break on deptno nodup
SQL> select deptno, sal from emp where deptno=10 order by sal;
    DEPTNO        SAL
---------- ----------
        10       1300
                 2450
                 5000

3 rows selected.

Regards
Michel
Re: Rank [message #447527 is a reply to message #447526] Mon, 15 March 2010 13:14 Go to previous message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Thanks Michel

Regards,
Ritesh
Previous Topic: Hide declaration or declare it inside the body of a package
Next Topic: search spaces in column
Goto Forum:
  


Current Time: Sun Sep 25 23:13:15 CDT 2016

Total time taken to generate the page: 0.08054 seconds