Home » SQL & PL/SQL » SQL & PL/SQL » Rank function (merged)
Rank function (merged) [message #255487] Wed, 01 August 2007 01:03 Go to next message
Raaghav
Messages: 5
Registered: August 2007
Location: INDIA
Junior Member

Hi every one,

I would like to know how to generate ranks for the employees basing on their salaries in descending order.

Regards,
Raaghav.

[Updated on: Wed, 01 August 2007 01:04]

Report message to a moderator

Re: Rank function [message #255489 is a reply to message #255487] Wed, 01 August 2007 01:06 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It depends on how you want to treat equal salaries. I'd use the RANK() or DENSE_RANK() analytic functions.

Look at those manuals. The example you're looking for is in there.

MHE
Re: Rank function [message #255490 is a reply to message #255487] Wed, 01 August 2007 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at row_number, rank and dense_rank functions depending on how you want to handle duplicate salaries.

(SQL Reference, Chapter 5 Functions)

Regards
Michel
Re: Rank function [message #255491 is a reply to message #255489] Wed, 01 August 2007 01:09 Go to previous messageGo to next message
Raaghav
Messages: 5
Registered: August 2007
Location: INDIA
Junior Member

Hi Maheer,

Thank you

Regards,
Raaghav.
Re: Rank function [message #255505 is a reply to message #255487] Wed, 01 August 2007 01:41 Go to previous messageGo to next message
nivas007
Messages: 7
Registered: July 2007
Location: Hydera bad
Junior Member

Hi Raaghav,

try this one once

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;



Regards,
Nivas..
RANK function [message #255510 is a reply to message #255487] Wed, 01 August 2007 01:48 Go to previous messageGo to next message
Raaghav
Messages: 5
Registered: August 2007
Location: INDIA
Junior Member

Hi every one,

I have one problem with RANK func. When I get two records with same salary say records 4 & 5, it is showing rank 4 for both the records. I would like to have unique ranks for them. Instead of giving rank 4 to both the records, I would like to give ranks 4 & 5.

Can any one tell me.

Regards,
Raaghav
Re: RANK function [message #255514 is a reply to message #255510] Wed, 01 August 2007 01:52 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read again my post.

Regards
Michel
Previous Topic: ORA-00917: missing comma
Next Topic: update else insert
Goto Forum:
  


Current Time: Fri Dec 02 14:15:46 CST 2016

Total time taken to generate the page: 0.12660 seconds