How to find second highest from emp(empno number, salary number)

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

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