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.
Full Threads Oldest First

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