Home » SQL & PL/SQL » SQL & PL/SQL » Wroking with DENSE_RANK function (Oracle 10G)
Wroking with DENSE_RANK function [message #400472] Tue, 28 April 2009 05:49 Go to next message
bholeuday
Messages: 28
Registered: April 2009
Location: Talavali
Junior Member

Hi,

i am working with DENSE_RANK function, i want below behaviour mark in red color

SELECT deptno,
ename,
sal,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM emp
WHERE deptno = 30;

DEPTNO ENAME SAL rank
---------- ---------- ---------- ----------
30 JAMES 950 1
30 WARD 1250 2
30 MARTIN 1250 2 - Here it should be 3
30 TURNER 1500 3
30 ALLEN 1600 4
30 BLAKE 2850 5

So i want DENSE_RANK to generate increasing number for the same sal person.
Is it possible???

Thanks in advance

Re: Wroking with DENSE_RANK function [message #400477 is a reply to message #400472] Tue, 28 April 2009 06:21 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What would you want in the next line, then?
30 TURNER 1500 3
Re: Wroking with DENSE_RANK function [message #400478 is a reply to message #400472] Tue, 28 April 2009 06:25 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
... and why should Ward get 2 and Martin 3?
Re: Wroking with DENSE_RANK function [message #400493 is a reply to message #400478] Tue, 28 April 2009 07:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming that you want results like this:
30 JAMES 950 1
30 WARD 1250 2 
30 MARTIN 1250 3
30 TURNER 1500 4
30 ALLEN 1600 5
30 BLAKE 2850 6
, you want to use Row_Number() over (order by sal asc, name desc)

[change formatting]

[Updated on: Tue, 28 April 2009 07:12]

Report message to a moderator

Re: Wroking with DENSE_RANK function [message #400505 is a reply to message #400493] Tue, 28 April 2009 09:04 Go to previous message
bholeuday
Messages: 28
Registered: April 2009
Location: Talavali
Junior Member

Hi JRowbottom,

Thanks, This is what i require.
Previous Topic: global variable multiple session
Next Topic: Lost - is it possible
Goto Forum:
  


Current Time: Tue Feb 18 11:13:38 CST 2025