Loading
View RSS Feed

Harsh Chowdhary's Blog

How To Query Nth Largest Value From A Table?

Rate this Entry
Below is the generalized query to do this:-

Code:
SELECT LEVEL, MAX('COL_NAME') FROM MY_TABLE WHERE LEVEL = '&N' CONNECT BY PRIOR ('COL_NAME') > 'COL_NAME') GROUP BY LEVEL;

Example:


Getting the data in the emp table:-

Code:
SQL> SELECT * FROM EMP;
EMPNO ENAME DEPTNO SAL
---------- ------------------------- ---------- ----------
1 jaya 10000 45000
2 suci 10000 35000
3 durga 15000 35000
4 manju 35000 9000

Now we will find out the Nth Maximum salary as follows:-

Code:
SQL> SELECT LEVEL, MAX(SAL) FROM EMP WHERE LEVEL = '&N' CONNECT BY PRIOR (SAL) > SAL GROUP BY LEVEL; 
 /
Enter value for n: 2
old 2: where level = '&n'
new 2: where level = '2'

LEVEL MAX(SAL)
---------- ----------
2 35000


If u need emp details along with the second max salary and also want to display duplicates, then use:

Code:
SQL> SELECT * FROM (SELECT ENAME,SAL,DENSE_RANK() OVER(ORDER BY SAL DESC) RANK FROM EMP) WHERE RANK=&N;
Enter the value for n: 2
old 2: where level = '&n'
new 2: where level = '2'

ENAME SAL RANK
------------------------- ---------- ----------
suci 35000 2
durga 35000 2


I hope the above queries are quite helpful for you.
There is one more query for the purpose which I have discussed in the last section of the blog post "How To Query Second Largest Value In A Column".
Let me know for any concerns..

Submit "How To Query Nth Largest Value From A Table?" to Digg Submit "How To Query Nth Largest Value From A Table?" to del.icio.us Submit "How To Query Nth Largest Value From A Table?" to StumbleUpon Submit "How To Query Nth Largest Value From A Table?" to Google

Updated 12-04-2010 at 03:23 AM by Harsh

Tags: database, oracle, sql
Categories
Oracle

Comments




Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.