Help In query

[Dharmendra patel]

Dear All ,

I have one query which give below result.
> select repcol,value,ra nk() 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

I would like to order repcol column based on maximum values present in
value column.


A has max values 500 and
b has max values 1000 so output should be like below:


REPCOL VALUE RANKED_VALUE
---------- ---------- ------------


B 1000 1
B 900 2
B 800 3
B 500 4
B 400 5
A 500 1
A 300 2
A 200 3
A 100 4

Plz let me know query to achive above result.

regards,
Dharmendra

[amitpatel66]

try out the below code:

select repcol,value,rank() over ( partition by repcol

order by value desc ) ranked_value

from test order by repcol desc

[Dharmendra patel]

try out the below code:

select repcol,value,rank() over ( partition by repcol

order by value desc ) ranked_value

from test order by repcol desc

-----------------------
Hi Amit

it is not fullfill my need. I want order of repcol based on values present for rank 1.

Thanks for try.


Regards,
Dharmendra

[amitpatel66]

Hi Dharmendra,

Not able to make it out in a single Query...Probabl y need to write a PLSQL block.....

Amit

[qhjghz]

-----------------------
Hi Amit

it is not fullfill my need. I want order of repcol based on values present for rank 1.

Thanks for try.


Regards,
Dharmendra

This is the solution:

select a.empno, a.ename, a.deptno, a.sal,b.max_sal ,
dense_rank() over (partition by a.deptno order by a.sal) dept_rank
from emp a,(select deptno,max(sal) max_sal from emp group by deptno) b
where
a.deptno=b.dept no
ORDER BY 5 DESC