You are html tracking Visitor

Sunday, July 20, 2008

How to Select the Nth highest / lowest value from a table:-

I am posting this script as lot of my friends have asked about this. They are lot of ways to do this. I am showing you in two best ways.

Note:- In 'N' place, provide your Nth number you want to findout.

How to Select the Nth highest value from a table:-
-------------------------------------------------

select level, max(sal) from scott.emp
where level = '&n'
connect by prior (sal) > sal
group by level;

How to select the Nth lowest value from a table:-
-------------------------------------------------------
select level, min(sal) from scott.emp
where level = '&n'
connect by prior (sal) <>N th Top Salary
-------------------

select a.ename,a.sal
from emp a
where n = (select count(distinct(b.sal))
from emp b where a.sal <= b.sal) N th Least Salary
---------------------
select a.ename,a.sal
from emp a
where n = (select count(distinct(b.sal))
from emp b
where a.sal >= b.sal)

68 comments:

Unknown said...

Hi
Can anyone explain me the concept of level, level = '&n' ,connect by prior used here.

regards
-navin

phani said...

Hi Navin,

Level is the Pseudo columns.

LEVEL - Returns a number indicating the level in the hierarchy: 1 for a root row, 2 for a child of a root, and so on.

Oracle Parent-child relation is design this way.

CONNECT BY is the condition that identifies the relationship between
parent and child rows of the hierarchy.

Using the above relationship, we can get results we wish in the hierarchy.

Regards,
Phani

Melisa said...


The information you have given here is truly helpful to me. CCNA- It’s a certification program based on routing & switching for starting level network engineers that helps improve your investment in knowledge of networking & increase the value of employer’s network,
Regards,
ccna course in Chennai|ccna training in Chennai

Priya Rajendran said...

Interesting article to read.. I got some useful information from this post,thank you for sharing
best hadoop training in chennai | best big data training in chennai

Oracle training in Chennai said...

Well explained. Thanks to shared this informative details with us. Keep updating.
DBA course syllabus | DBA training courses

tansitanu said...

I feel really happy to have seen your webpage and look forward to so many more entertaining times reading here. Thanks once more for all the details.
google-cloud-platform-training-in-chennai



simbu said...

Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
java training in omr

java training in annanagar

java training in chennai

java training in marathahalli

java training in btm layout

java training in rajaji nagar

java training in jayanagar

Unknown said...

It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
python training in omr

python training in annanagar | python training in chennai

python training in marathahalli | python training in btm layout

python training in rajaji nagar | python training in jayanagar

pooja said...

Great content thanks for sharing this informative blog which provided me technical information keep posting.

Hadoop Training in Chennai

Hadoop Training in Bangalore

Big data training in tambaram

Big data training in Sholinganallur

Big data training in annanagar

Big data training in Velachery

Big data training in Marathahalli

SRI said...

Thanks for the informative article. This is one of the best resources I have found in quite some time. Nicely written and great info. I really cannot thank you enough for sharing.

rpa training in marathahalli

rpa training in btm

rpa training in kalyan nagar

rpa training in electronic city

rpa training in chennai

rpa training in pune

rpa online training

Unknown said...

This is very good content you share on this blog. it's very informative and provide me future related information.
Data Science training in btm
Data Science training in rajaji nagar
Data Science training in chennai
Data Science training in kalyan nagar
Data Science training in electronic city
Data Science training in USA
selenium training in chennai
selenium training in bangalore

Unknown said...
This comment has been removed by the author.
Unknown said...

Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.

rpa training in Chennai | rpa training in pune

rpa training in tambaram | rpa training in sholinganallur

rpa training in Chennai | rpa training in velachery

rpa online training | rpa training in bangalore

sai said...

Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.
Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.

Unknown said...

I am definitely enjoying your website. You definitely have some great insight and great stories. 
java training in chennai | java training in bangalore


java training in tambaram | java training in velachery

sai said...

Thank you for allowing me to read it, welcome to the next in a recent article. And thanks for sharing the nice article, keep posting or updating news article.
python online training
python training in OMR
python training in tambaram

Unknown said...

Fantastic work! This is the type of information that should follow collective approximately the web. Embarrassment captivating position Google for not positioning this transmit higher! Enlarge taking place greater than and visit my web situate

Data Science training in rajaji nagar | Data Science with Python training in chenni
Data Science training in electronic city | Data Science training in USA
Data science training in pune | Data science training in kalyan nagar

Anonymous said...

I love the blog. Great post. It is very true, people must learn how to learn before they can learn. lol i know it sounds funny but its very true. . .


angularjs Training in chennai
angularjs-Training in pune

angularjs-Training in chennai

angularjs Training in chennai

angularjs-Training in tambaram

gowthunan said...

I’d love to be a part of group where I can get advice from other experienced people that share the same interest. If you have any recommendations, please let me know. Thank you.
iosh course in chennai

Unknown said...

Impressive. Your story always bring hope and new energy. Keep up the good work.
Java training in Chennai | Java training in Tambaram | Java training in Chennai | Java training in Velachery

Java training in Chennai | Java training in Omr | Oracle training in Chennai

Mounika said...

Appreciating the persistence you put into your blog and detailed information you provide
online Python training | python training in chennai | Data science training in bangalore

Mounika said...

Thank you for allowing me to read it, welcome to the next in a recent article. And thanks for sharing the nice article, keep posting or updating news article.
online Python training | python training in chennai | Data science training in bangalore

Unknown said...

Wonderful article, very useful and well explanation. Your post is extremely incredible. I will refer this to my candidates...

Data Science Course in Indira nagar | Data Science Course in btm layout

Python course in Kalyan nagar | Data Science course in Indira nagar

Data Science Course in Marathahalli | Data Science Course in BTM Layout

afiah b said...

I believe there are many more pleasurable opportunities ahead for individuals that looked at your site.
Java training in Chennai | Java training in Bangalore

Java interview questions and answers | Core Java interview questions and answers

Mounika said...

Thank you for benefiting from time to focus on this kind of, I feel firmly about it and also really like comprehending far more with this particular subject matter. In case doable, when you get know-how, is it possible to thoughts modernizing your site together with far more details? It’s extremely useful to me 
python training institute in marathahalli
python training institute in btm
Python training course in Chennai

ganga said...

Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you..Keep update more information..
angularjs Training in bangalore

angularjs Training in btm

angularjs Training in electronic-city

angularjs online Training

angularjs Training in marathahalli

dwarakesh said...

Greetings. I know this is somewhat off-topic, but I was wondering if you knew where I could get a captcha plugin for my comment form? I’m using the same blog platform like yours, and I’m having difficulty finding one? Thanks a lot.

Advanced AWS Amazon Web Services Interview Questions And Answers

Best AWS Tutorial |Learn Best Amazon Web Services Tutorials |Advanced AWS Tutorial For Beginners


Best AWS Online Training | No.1 Online AWS Certification Course - Gangboard

Best AWS Training in Toronto| Advanced Amazon Web Services Training in Toronto, Canada

Unknown said...

The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
Core Java interview questions and answers

Java training in Chennai | Java training in Tambaram

Java training in Chennai | Java training in Velachery

Java training in Chennai | Java training in Omr

DeepikaOrange said...

Wonderful information, thanks a lot for sharing kind of information. Your website gives the best and the most interesting information. Thanks a ton once again

Oracle DBA Training
Oracle PLSQL Training
Oracle Performance Tunning Training

pragyachitra said...

I really like your blog. You make it interesting to read and entertaining at the same time. I cant wait to read more from you.
angularjs Training in bangalore

angularjs Training in btm

angularjs Training in electronic-city

angularjs online Training

angularjs Training in marathahalli

angularjs interview questions and answers

Anbarasan14 said...

This was helpful to me thanks for sharing this useful information. Kindly continue the work.

IELTS Training in Adyar
IELTS COaching Classes in Besant Nagar
IELTS Classes in Palavakkam
IELTS Coaching Center in Chennai Anna Nagar
IELTS Classes in Anna Nagar West
IELTS Training Institute near me
IELTS Training in Chennai Ayanavaram

rama said...

Thanks for information , This is very useful for me.
Keep sharing Lean Six Sigma Green Belt Training Bangalore


Abidivya said...

I appreciate your efforts because it conveys the message of what you are trying to say. It's a great skill to make even the person who doesn't know about the subject could able to understand the subject . Your blogs are understandable and also elaborately described. I hope to read more and more interesting articles from your blog.
rpa training in bangalore
best rpa training in bangalore
best rpa training in bangalore
rpa training in pune
rpa training in chennai

Unknown said...

Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article. thank you for sharing such a great blog with us.
rpa training in bangalore
best rpa training in bangalore
rpa training in pune
rpa online training

Jaweed Khan said...

Thanks For Sharing your Information The Information Shared Is Very Valuable Please Keep Updating Us Time Just Went On reading the article Data Science Online Training Hadoop Science Online Training AWS Online Training Python Online Training

rose said...

I really appreciate this post. I’ve been looking all over for this! Thank goodness I found it on Bing. You’ve made my day! Thx again!

Microsoft Azure online training
Selenium online training
Java online training
Java Script online training
Share Point online training


Rigid Box said...


Information from this blog is very useful for me, am very happy to read this blog Kindly visit us @ Luxury Watch Box | Shoe Box Manufacturer |  Candle Packaging Boxes

Jagadeesh said...
This comment has been removed by the author.
Prwatech said...

Your info is really amazing with impressive content..Excellent blog with informative concept. Really I feel happy to see this useful blog, Thanks for sharing such a nice blog..
If you are looking for any python Related information please visit our website python training institutes in Bangalore page!

Imran said...

Amazing stuff
SAP Training in Chennai
SAP ABAP Training in Chennai
SAP Basis Training in Chennai
SAP FICO Training in Chennai
SAP MM Training in Chennai
SAP PM Training in Chennai
SAP PP Training in Chennai
SAP SD Training in Chennai

salman said...

https://vodafonecustomercarenumber.hatenablog.com
https://vodafonecustomercarenumber.hatenablog.com
https://mpcustomercareno.blogspot.com
https://mpcustomercareno.blogspot.com
https://myairtelcustomercarenumber.blogspot.com
https://myairtelcustomercarenumber.blogspot.com

nash b said...

Nice...
snowflake interview questions and answers

inline view in sql server

a watch was sold at loss of 10

resume format for fresher lecturer in engineering college doc

qdxm:sfyn::uioz:

java developer resume 6 years experience

please explain in brief why you consider yourself suitable for the position applied for

windows 10 french iso kickass

max int javascript

tp link router password hack

Training for IT and Software Courses said...

Really it was an awesome article,very interesting to read.You have provided an nice article,Thanks for sharing.google cloud platform training in bangalore

raju said...

awesome...!!
internship for ece students
r programming training in chennai
internship in chennai
ccna training in chennai
internship at chennai
inplant training in chennai
dotnet training in chennai
android training in chennai
oracle training in chennai
matlab training in chennai

Sowmiya R said...

Thanks for sharing,this blog makes me to learn new thinks.
interesting to read and understand.keep updating it.

Oracle Training | Online Course | Certification in chennai | Oracle Training | Online Course | Certification in bangalore | Oracle Training | Online Course | Certification in hyderabad | Oracle Training | Online Course | Certification in pune | Oracle Training | Online Course | Certification in coimbatore

aarthi said...

The training regarding oracle is very much useful.Keep updating.
Java training in Chennai

Java training in Bangalore

Java training in Hyderabad

Java Training in Coimbatore

Java Online Training

Devi said...

Thank you for valuable information.I am privilaged to read this post. oracle training in chennai

Jayalakshmi said...

Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging.
angular js training in chennai

angular js training in tambaram

full stack training in chennai

full stack training in tambaram

php training in chennai

php training in tambaram

photoshop training in chennai

photoshop training in tambaram

jeni said...

I have express a few of the articles on your website now, and I really like your style of blogging. I added it to my favorite’s blog site list and will be checking back soon…
oracle training in chennai

oracle training in velachery

oracle dba training in chennai

oracle dba training in velachery

ccna training in chennai

ccna training in velachery

seo training in chennai

seo training in velachery

shiny said...

Very interesting blog. Many blogs I see these days do not really provide anything that attracts others, but believe me the way you interact is literally awesome


angular js training in chennai

angular js training in annanagar

full stack training in chennai

full stack training in annanagar

php training in chennai

php training in annanagar

photoshop training in chennai

photoshop training in annanagar

deiva said...

Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
hadoop training in chennai

hadoop training in omr

salesforce training in chennai

salesforce training in omr

c and c plus plus course in chennai

c and c plus plus course in omr

machine learning training in chennai

machine learning training in omr

EXCELR said...

Excellent article. Very interesting to read. I really love to read such a nice article. Thanks! keep rocking Best data science courses in hyerabad

Anonymous said...

Excellent article on Orcale app. See how playing 11 is riding this IPL season

Anonymous said...

Stacydoe.com is the place where you can buy vouchers, coupons for all advertisement needs of online advertising.

Free CSEET Online Classes said...

You can't imagine what I am going to tell you. Our institution is offering you a CS executive classes and a free of cost CSEET classes and many more to explore. So please contact us or visit our website at https://uniqueacademyforcommerce.com/

kishor said...

best digital marketing agancy new tips for best digital marketing
free classified submission sites list
kishorsasemahal

Anonymous said...

Do you believe in long term investement . One of the option of doing investement is by investing in Crypto currencies. You can invest in Fudxcoin company that deals in the selling and purchasing of Crypto Currency. It is a reliable company. One need not doubt in investing in it as i have also bought crypto currency from it and feeling very satisfied with their services.
crypto currency block chain technology



VIPTHANOSS said...

aqualudo

SwarnApp said...

This is really a nice and informative.
Jewellery Billing Software
Jewellery Billing Software

SwarnApp said...

This is really a nice and informative.
Jewellery ERP Software UAE
Jewellery ERP Software UAE

Bhumi DM said...

Thanks for this helpful guide on selecting the Nth highest or lowest values! Your explanations and examples make it much easier to understand the SQL queries involved.

Data Science Courses in Brisbane

prachu said...

Great article. Your practical tips on highest or lowest value were exactly what I needed. Such a informative article that would be super helpful for those of us just getting started.
https://iimskills.com/data-science-courses-in-westminster/


Data Science said...

The comment section is filled with a mix of feedback, questions, spam, and self-promotions. A common theme in the constructive comments is the appreciation for tutorials and explanations around SQL, specifically focusing on concepts like hierarchical queries with CONNECT BY in Oracle, the use of LEVEL for hierarchical data, and selecting the Nth highest or lowest values. Several users mention how helpful the explanations have been, particularly for beginners in SQL or those preparing for Oracle certifications.

However, much of the later comments deviate from the technical content and include promotional links for various unrelated training courses, certifications, and services, making it a mix of on-topic feedback and off-topic advertisements. This seems to have escalated over the years, with more recent comments being largely promotional. Data science courses in Gurgaon

IIMSkillsAnubhutiV said...

good article
Data science Courses in London

Bhumi IIM Skills said...

Thanks for this helpful guide on selecting the Nth highest or lowest value! Your examples make the SQL queries much clearer and easier to implement.
Data science courses in Dubai

Sadhvi said...

Thanks for sharing these SQL scripts! Selecting the Nth highest or lowest value can often be a tricky task, and your examples provide clear and effective methods for doing so. The use of hierarchical queries with CONNECT BY PRIOR is a great technique for this, especially in Oracle databases. I appreciate the inclusion of both highest and lowest value queries—this will definitely help those looking to deepen. Data science courses in Visakhapatnam

Data Analytics Courses In Ontario said...

"Great read! Data science is such a dynamic field, and having a reliable list of courses is invaluable. The Data Science courses in Brighton are definitely worth exploring for anyone in the area who wants to upskill and grow their career."

Rachana said...

Thanks for sharing these practical SQL queries! Selecting the Nth highest or lowest value can be tricky, and your examples provide clear solutions. This is definitely helpful for anyone working with databases. Great post!
Data science courses in Gujarat