Problem solve Get help with specific problems with your technologies, process and projects.

Ranking in Oracle 8.1.6

See how to rank data in a table in a specified order in Oracle 8.1.6.

Have you ever wanted to rank data in a table in a specified order? Here's how to do it in Oracle 8.1.6: Let's say that you have a table called "test" and you want to rank the values in "repcol."

SQL> select * from test;

REPCOL          VALUE
---------- ----------
A                 100
A                 200
A                 300
B                1000
B                 900
B                 800
A                 500
B                 400
B                 500

SQL> select repcol,value,rank() over ( partition by repcol
  2  order by value desc ) ranked_value
  3  from test;

REPCOL          VALUE RANKED_VALUE
---------- ---------- ------------
A                 500            1
A                 300            2
A                 200            3
A                 100            4
B                1000            1
B                 900            2
B                 800            3
B                 500            4
B                 400            5
 

Code: SQL> select repcol,value,rank() over ( partition by repcol
  2  order by value desc ) ranked_value
  3  from test;

REPCOL          VALUE RANKED_VALUE
---------- ---------- ------------
A                 500            1
A                 300            2
A                 200            3
A                 100            4
B                1000            1
B                 900            2
B                 800            3
B                 500            4
B                 400            5

For More Information


Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close