select dr rank, sal
from (
select sal,
row_number()over(partition by sal order by sal desc) rn,
dense_rank()over(order by sal desc) dr
from emp
)
where rn = 1 and dr <= 2
/

RANK SAL
---------- ----------
1 5000
2 3000


-----Original Message-----
From: Manish Deshwal
[mailto:oracledba-ezmlmshield-x63412818.[Email address protected]
Sent: Monday, April 30, 2007 7:27 AM
To: LazyDBA Discussion
Subject: RE: max rows


Hey Ed,

That sounds good but also complicated too to me!!! Can you explain that
a bit..Thanks..

Thanks & Regards,
Manish Deswal


-----Original Message-----
From: Edwards Ed
[mailto:oracledba-ezmlmshield-x1116155.[Email address protected]
Sent: Monday, April 30, 2007 4:43 PM
To: LazyDBA Discussion
Subject: RE: max rows

Hey Imran Sheikh,
This might help.

select dr rank, sal
from (
select sal,
row_number()over(partition by sal order by sal desc) rn,
dense_rank()over(order by sal desc) dr
from emp
)
where rn = 1 and dr <= 2
/





Please take a few minutes to provide feedback on the quality of service
you received from our staff. The Department of Education values your
feedback as a customer. Commissioner of Education Jeanine Blomberg is
committed to continuously assessing and improving the level and quality
of services provided to you.Simply use the link below. Thank you in
advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




-----Original Message-----
From: Imran Sheikh
[mailto:oracledba-ezmlmshield-x29827903.[Email address protected]
Sent: Monday, April 30, 2007 7:02 AM
To: LazyDBA Discussion
Subject: max rows

Dear all,

Can I get two max rows in a query like:



Select max(col1)

From table1



Now if it returns 10 rows I want to get only 2 highest value rows.



Thanks,





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



========================================================================
====================================================

Disclaimer:

This message and the information contained herein is proprietary and
confidential and subject to the Tech Mahindra policy statement, you may
review at <a
href="http://www.techmahindra.com/Disclaimer.html">http://www.techmahind
ra.com/Disclaimer.html</a> externally and <a
href="http://tim.techmahindra.com/Disclaimer.html">http://tim.techmahind
ra.com/Disclaimer.html</a> internally within Tech Mahindra.

========================================================================
====================================================


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

Oracle LazyDBA home page