# Women in Technology

Hear us Roar

 Article: SQL Data Types Subject: how do we find nth highest in SQL Date: 2004-10-08 07:32:14 From: gansin Please give the query for the finding the nth highest salary in SQL.

Showing messages 1 through 25 of 25.

• how do we find nth highest in SQL
2004-10-13 04:11:13  sunrek [View]

• how do we find nth highest in SQL

To find the nth highest salary as below

select max(a.sal) from emp a where &n=(select count(b.sal) from emp b where (a.sal output-
put the value of n and get the highest salary that u have give the number i;e 4,5,6,10 etc.

thanx,
Haribrat

• how do we find nth highest in SQL

Query for Nth Max (Highest)
select sal from emp t
where &n = (select count(sal)
from (select distinct sal from emp)
where t.sal<=sal);

<b>Query for Nth Min (Lowest)
select sal from emp t
where &n = (select count(sal)
from (select distinct sal from emp)
where t.sal>=sal);
• query for nth max
2007-08-25 02:05:46  kashif094 [View]

select * from emp e where 1=(select count(distinct sal) from emp where e.sal<=sal)

hi i am kashif can anyone tell me what is the concept of 1 in this query?
• query for nth max
2007-10-25 05:02:21  jolly_cet [View]

1 specifies the position..
• how do we find nth highest in SQL

Query for Nth Max (Highest)
select sal from emp t
where &n = (select count(sal)
from (select distinct sal from emp)
where t.sal<=sal);

<b>Query for Nth Min (Lowest)
select sal from emp t
where &n = (select count(sal)
from (select distinct sal from emp)
where t.sal>=sal);
• how do we find nth highest in SQL
2005-01-18 21:03:32  hiexplain [View]

thanks a lot
"select sal from emp t
where &n = (select count(sal)
from (select distinct sal from emp)
where t.sal<=sal);"
it is working fine
could u please let me know how it works...
thanks and reguards
surendra
• how do we find nth highest in SQL
2005-10-17 23:31:59  Apurva_Sharma [View]

Hi,

Steps
SQL> SET Verify OFF;

SQL> SELECT min(sal)
FROM (SELECT sal
FROM emp
order by sal desc)
WHERE rownum<(&nth_heighest+1);
• how do we find nth highest in SQL
2005-01-28 13:02:12  parangogoi [View]

• how do we find nth highest in SQL
2005-10-17 23:29:32  Apurva_Sharma [View]

Hi,

Steps
SQL> SET Verify OFF;

SQL> SELECT min(sal)
FROM (SELECT sal
FROM emp
order by sal desc)
WHERE rownum<(&nth_heighest+1);
• how do we find nth highest in SQL

select * from emp e where 1=(select count(distinct sal) from emp where e.sal<=sal)
if u want to print the second highest sal. replace 1 with 2.
• how do we find nth highest in SQL
2006-10-17 02:09:39  Asha_S [View]

very useful query for interviews... thanks
• how do we find nth highest in SQL
2010-05-11 00:05:08  vish44 [View]

This query only work till 3rd highest, after that it gives 4th highest when we set n=7
• how do we find nth highest in SQL
2005-09-25 23:55:04  lalitpant [View]

• how do we find nth highest in SQL
2005-10-17 23:28:21  Apurva_Sharma [View]

Hi,

Steps
SQL> SET Verify OFF;

SQL> SELECT min(sal)
FROM (SELECT sal
FROM emp
order by sal desc)
WHERE rownum<(&nth_heighest+1);
• how do we find nth highest in SQL
2009-10-13 12:05:05  Sandip D [View]

HI Apporva,
I think this is also correct,
but can't u try this out, which is more simple :-

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

Enter value for n: 2
SAL
---------
3700
• how do we find nth highest in SQL
2006-05-19 05:24:47  SPKUMAR [View]

ok
• how do we find nth highest in SQL
2006-07-11 22:39:42  prasanna1729 [View]

how do we find nth highest in SQL
• how do we find nth highest in SQL
2006-08-17 04:21:27  navingujar [View]

One more way, select min(sal) from (select distinct sal from emp order by sal desc) where rownum <=n
• how do we find nth highest in SQL
2006-11-13 01:13:42  viren_19 [View]

• how do we find nth highest in SQL
2006-11-13 01:13:53  viren_19 [View]

how do we find nth highest in SQL
• how do we find nth highest in SQL
2006-08-17 04:21:32  navingujar [View]

One more way, select min(sal) from (select distinct sal from emp order by sal desc) where rownum <=n
• how do we find nth highest in SQL
2007-11-21 02:20:53  Ani123 [View]

With qry1 as (Select col1, col2, rownumber() over ()rownum from tablename order by col2 DESC)
select * from qry1 where rownum=6;

This finds the 6th highest;
• how do we find nth highest in SQL
2009-10-13 11:57:45  Sandip D [View]

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Enter value for n: 2
SAL
---------
3700
• how do we find nth highest in SQL
2010-03-06 20:24:30  Sikindar [View]

SELECT DISTINCT (a.sal) FROM EMP A
WHERE &N = (SELECT COUNT (DISTINCT b.sal)
FROM EMP B WHERE a.sal<=b.Sal);

Enter value for n: 2
SAL
-------
3000