To All,
You can find many outstanding tips on using SQL in the book title "SQL
Cookbook" by Anthony Molinaro! Get your copy today!

Have a great Day!




-----Original Message-----
From: Edwards Ed
[mailto:oracledba-ezmlmshield-x14307678.[Email address protected]
Sent: Monday, April 30, 2007 7:58 AM
To: LazyDBA Discussion
Subject: RE: max rows

--Locating maximum value
SELECT * FROM emp a
WHERE &N = (SELECT count(DISTINCT(b.sal))
FROM emp b WHERE a.sal <= b.sal)
/


--Locating minimum value
SELECT * FROM emp a
WHERE &N = (SELECT count(DISTINCT(b.sal))
FROM emp b WHERE a.sal <= b.sal)
/


If N =1 will return first max or first min,
then N = 2 will return second max or min, and so on.


You know what I mean Vern? Hehe.


It's Fridayyyyyyyyy! Partyyyyyyyyyy!


Hot fun in the summer time! Hehe.


Have a great Day!






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: Edwards, Ed
Sent: Monday, April 30, 2007 7:13 AM
To: 'Imran Sheikh '; LazyDBA Discussion
Subject: RE: max rows
Importance: High

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
/


-----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

Oracle LazyDBA home page