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

• 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: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
  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:53  viren_19 [View]
  
  
  how do we find nth highest in SQL
• 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
  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,
  Solution to your query.Plz follow steps.
  
  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
  2005-01-28 13:02:12  parangogoi [View]
  
  
  Please help me with the above query
• how do we find nth highest in SQL
  2005-10-17 23:29:32  Apurva_Sharma [View]
  
  
  Hi,
  Solution to your query.Plz follow steps.
  
  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-10-30 09:21:35  bhaskarreddy [View]
  
  
  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
  2004-10-13 04:11:13  sunrek [View]
  
  
  can u please help me
  
• how do we find nth highest in SQL
  2005-10-17 23:31:59  Apurva_Sharma [View]
  
  
  Hi,
  Solution to your query.Plz follow steps.
  
  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
  2004-11-14 21:40:07  Question&answer [View]
  
  
  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
  2004-12-11 08:17:00  RuchirAwadhawal [View]
  
  
  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
  2004-12-11 08:16:41  RuchirAwadhawal [View]
  
  
  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..