chaitanya k
How to find second highest from emp(empno number, salary number)
By chaitanya k in ASP.NET on Dec 07 2007
  • Devesh Omar
    Nov, 2013 14

    Using Dense rank in sql(SELECT name , salary , DENSE_RANK() over (order by salary desc) AS Rowno FROM emp) a Where a.Rowno=NWhere N may be 2, 3 etc

    • 0
  • Venkatesh Kumar 0
    Mar, 2008 6

    Here we can use self join for EMP table. The following query would work for not only 2nd highest salary. It will work for nth highest record or salary also.

    SELECT E1.empno,E1.salary from Emp E1 WHERE

    (N-1) = (SELECT COUNT(DISTINCT(E2.salary)) FROM Emp E2 WHERE

    E2.salary > E1.Salary)

    In this query you can replace N value to any number that you want the maximum.

    By

    Jollykid

    • 0
  • srinivasu N
    Dec, 2007 29

    It works fine.(in EMP table empid,empname,salary are field names)

    select empid,empname,salary from emp where salary=(select top 1 salary from (select distinct top 2 salary from emp order by salary desc) a order by salary)

    vasu.s

    • 0


Most Popular Job Functions


MOST LIKED QUESTIONS