How to seach a prevoius post

Quote:

---

Did I miss something here?..
select empno,ename,sal from (select empno,ename,sal, rank()
over(order by sal desc) sal1 from emp)
where rownum<=5000;

---

Again u r using rownum in the where clause , just forget the rownum,
now the rank function will order ur salary and the ranks are in
SAL1 ( *** NOT sal) , so in where clause ask for top 10 rankers .

so it shud b like this

select empno,ename,sal from (select empno,ename,sal, rank()
over(order by sal desc) sal1 from emp)
where sal1<=10 ;

here sal1 is not the salary column ie ( SAL in ur query , u r talking abt MAX(sal) and MIN(sal) , iam talking abt sal1 )this is rank, u shud get the top 10 rank holders,

NO MORE ROWNUM , if u want to do it with rownum u don need RANK(),in tat case u can use the query given by Stmontgo in his reply.

hope this works for u .......

Akila Rajakumar

oops just a typo...i actually used sal<=5000...but not the sal1 inner...wait...i run it again

Hi Kuya....

Yes, i got it. it worked now....Thanks a lot again...

I just wonder which code is efficient and runs faster, so i compare the two.

I set time on, so i can see ellapsed time for the 2 programs.
I found out that the simpler code took 5 secs. to process 500,000 rows
while the code with function took 10 secs.

02:07:51 SQL> select rops_amt from
02:07:52 2 (select rops_amt from geninfo where rops_amt is not null order by rops_amt desc)
02:07:52 3 where rownum<11;

ROPS_AMT
----------
315120
252520
250000
193870
181960
177940
169420
164620
161710
159970

10 rows selected.

02:07:57 SQL>
02:08:13 SQL> select rops_amt from (select rops_amt, rank() over(order by rops_amt desc) rops_amt1
02:08:14 2 from geninfo where rops_amt is not null) where rops_amt1<=10;

ROPS_AMT
----------
315120
252520
250000
193870
181960
177940
169420
164620
161710
159970

10 rows selected.

02:08:24 SQL>

:D luv yah all

The problem is that those two queries are two totaly different things, i.e. they answer two totaly different questions! You are comparing apples to oranges.

It is just coincidence that you've got identical results from both of them - but in general they return two different resultsets.

oh i see...thanks for the info kuya...maybe i have to read more about
this rank() function to know when is it appropriate to use. I believe
it not suited for the report i want to obtain.:D