Home » SQL & PL/SQL » SQL & PL/SQL » equivalent to rank in 7.3
equivalent to rank in 7.3 [message #254659] Fri, 27 July 2007 06:48 Go to next message
manyal
Messages: 77
Registered: March 2005
Member
Dear Friends,

I am using oracle 7 as a backend i want to know is there any function equivalent to rank function(oracle 9i) in oracle 7.

thanx in advance.

Manish Manyal
Re: equivalent to rank in 7.3 [message #254662 is a reply to message #254659] Fri, 27 July 2007 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

Regards
Michel
Re: equivalent to rank in 7.3 [message #254768 is a reply to message #254662] Sat, 28 July 2007 01:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There is no equivalent to any of the Analytic functions. Some of the capabilities of analytic functions can be achieved by self-joins, otherwise you need to use PL/SQL.

For instance, if you didn't want to use the PARTITION BY clause of RANK(), you might be able to:

SELECT *
FROM (
  SELECT a.*, rownum AS rnk
  FROM (
    SELECT *
    FROM   my_table
    ORDER BY my_rank_col
  )
)
ORDER BY rnk


I can't remember whether 7.3 allows ORDER BY in inline views - it might not. People have overcome this in past with GROUP BY, which, providing you are not using 10.2+ or parallel query, will return a binary ascending order. This sort of thing is frowned upon though because it WILL break when you (eventually) upgrade the database.

Ross Leishman
Re: equivalent to rank in 7.3 [message #254774 is a reply to message #254768] Sat, 28 July 2007 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ross, you give the a "row_number" ersatz, "rank" or "dense_rank" is a little bit more difficult as you have to handle identical values.

Surely a good exercise for students... Cool

Regards
Michel
Re: equivalent to rank in 7.3 [message #254807 is a reply to message #254774] Sat, 28 July 2007 08:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Good point. I should have qualified "if you didn't want to use the PARTITION BY clause of RANK() and had no duplicate values, you might be able to..."

A pre-analytic functions solution I have seen is below. It does RANK() but not DENSE_RANK() - this one sould be adaptable to achieve a PARTITION BY equivalence as well. I've never used it because it has deplorable performance degradation over large data volumes. I would use PL/SQL 10 times out of 10.

SELECT a.pk, a.col1, ...., 1 + count(*) AS rnk
FROM   my_table a
,      my_table b
WHERE  b.my_rank_col < a.my_rank_col
GROUP BY a.pk, a.col1, ....


Ross Leishman
Re: equivalent to rank in 7.3 [message #254865 is a reply to message #254768] Sun, 29 July 2007 15:56 Go to previous message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
rleishman wrote on Fri, 27 July 2007 23:04
I can't remember whether 7.3 allows ORDER BY in inline views - it might not.



Oracle 8.0 didn't even allow an ORDER BY clause in an inline view.
Previous Topic: using bulk collect with select
Next Topic: Stale Updates and Explicit Cursors
Goto Forum:
  


Current Time: Sat Dec 03 10:00:48 CST 2016

Total time taken to generate the page: 0.09813 seconds