Home » SQL & PL/SQL » SQL & PL/SQL » how we create rank on column without using rownum function and rank function. (oracle 10g)
how we create rank on column without using rownum function and rank function. [message #579923] Sun, 17 March 2013 12:43 Go to next message
umeshchandraseth
Messages: 60
Registered: June 2011
Location: new delhi
Member
hi all,

can we create rank on a particular column without using rownum and rank function.


thanks in advance!
Re: how we create rank on column without using rownum function and rank function. [message #579924 is a reply to message #579923] Sun, 17 March 2013 12:48 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1794
Registered: January 2010
Senior Member
And why such restictions? You can scratch your left ear with your right hand, but why would you?

SY.
Re: how we create rank on column without using rownum function and rank function. [message #579925 is a reply to message #579923] Sun, 17 March 2013 14:13 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use ROW_NUMBER or DENSE_RANK. Wink

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

[Updated on: Sun, 17 March 2013 14:14]

Report message to a moderator

Re: how we create rank on column without using rownum function and rank function. [message #580043 is a reply to message #579925] Tue, 19 March 2013 12:57 Go to previous messageGo to next message
Bill B
Messages: 1049
Registered: December 2004
Senior Member
select col1,rownum rank
from 
(select col1
 from my_table
 order by col1);
Re: how we create rank on column without using rownum function and rank function. [message #580052 is a reply to message #580043] Tue, 19 March 2013 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
VERY VERY Good!
Hmmm... NO, very very BAD.
But maybe "without using rownum" does not hit something in you. Wink

Regards
Michel
Re: how we create rank on column without using rownum function and rank function. [message #580053 is a reply to message #580052] Tue, 19 March 2013 13:23 Go to previous messageGo to next message
Bill B
Messages: 1049
Registered: December 2004
Senior Member
I will throw myself on my sword now. LOL. I missed that! Thanks Michel.
Re: how we create rank on column without using rownum function and rank function. [message #580095 is a reply to message #580053] Wed, 20 March 2013 06:40 Go to previous messageGo to next message
_jum
Messages: 485
Registered: February 2008
Senior Member
These restrictions seems to be very "randomly".
You could use other analytic functions ROW_NUMBER or DENSE_RANK as @Michel said, and you can "mask" the usage a little with the WINDOWING_CLAUSE:
SELECT SUM(1) OVER (ORDER BY ename ROWS UNBOUNDED PRECEDING) rn, 
       empno, ename, job
  FROM emp;
rn      empno   ename   job
-----------------------------------
1	7876	ADAMS	CLERK
2	7499	ALLEN	SALESMAN
3	7698	BLAKE	MANAGER
4	7782	CLARK	MANAGER
5	7902	FORD	ANALYST
6	7900	JAMES	CLERK
7	7566	JONES	MANAGER
8	7839	KING	PRESIDENT
9	7654	MARTIN	SALESMAN
10	7934	MILLER	CLERK
11	7788	SCOTT	ANALYST
12	7369	SMITH	CLERK
13	7844	TURNER	SALESMAN
14	7521	WARD	SALESMAN
Re: how we create rank on column without using rownum function and rank function. [message #580103 is a reply to message #579924] Wed, 20 March 2013 07:48 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Solomon Yakobson wrote on Sun, 17 March 2013 17:48
And why such restictions? You can scratch your left ear with your right hand, but why would you?

SY.

Because I was holding my coffee in my left hand Razz
Re: how we create rank on column without using rownum function and rank function. [message #580109 is a reply to message #580095] Wed, 20 March 2013 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
SELECT SUM(1) OVER (ORDER BY ename ROWS UNBOUNDED PRECEDING) rn,


Good one. Smile
In the same way:
SQL> SELECT count(*) over (ORDER BY ename) rk, empno, ename, job
  2  from emp
  3  order by 1
  4  /
        RK      EMPNO ENAME      JOB
---------- ---------- ---------- ---------
         1       7876 ADAMS      CLERK
         2       7499 ALLEN      SALESMAN
         3       7698 BLAKE      MANAGER
         4       7782 CLARK      MANAGER
         5       7902 FORD       ANALYST
         6       7900 JAMES      CLERK
         7       7566 JONES      MANAGER
         8       7839 KING       PRESIDENT
         9       7654 MARTIN     SALESMAN
        10       7934 MILLER     CLERK
        11       7788 SCOTT      ANALYST
        12       7369 SMITH      CLERK
        13       7844 TURNER     SALESMAN
        14       7521 WARD       SALESMAN

Regards
Michel
Re: how we create rank on column without using rownum function and rank function. [message #580163 is a reply to message #580103] Thu, 21 March 2013 00:13 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Laughing

regards,
Delna
Previous Topic: Ref Cursors
Next Topic: Ref Cursors for passing parameters
Goto Forum:
  


Current Time: Fri Apr 18 19:28:56 CDT 2014

Total time taken to generate the page: 0.10247 seconds