second highest and avg salary

Question

How to find second highest salary and avg salary

Rajeesh Menoth · Answer

Hi, SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee ) An explanation of the solution The SQL above first finds the highest salary value in the Employee table using “(select MAX(Salary) from Employee)”. Then, adding the “WHERE Salary NOT IN” in front basically creates a new set of Salary values that does not include the highest Salary value. For instance, if the highest salary in the Employee table is 200,000 then that value will be excluded from the results using the “NOT IN” operator, and all values except for 200,000 will be retained in the results. This now means that the highest value in this new result set will actually be the 2nd highest value in the Employee table. So, we then select the max Salary from the new result set, and that gives us 2nd highest Salary in the Employee table. And that is how the query above works. Reference : http://www.programmerinterview.com/index.php/database-sql/find-nth-highest-salary-sql/ http://javarevisited.blogspot.in/2012/12/how-to-find-second-highest-or-maximum-salary-sql.html The answer is helpful.Then please accept it ..!!

Ravi Patel · Answer

Hi, For Second Highest salary select max(salarycolumnName) from dbo.tableName where salarycolumnName for Average Salary select AVG(SalaryColumnName)as 'AvgSalary' from dbo.TableName

Supreeth J · Answer

For 2nd Highest Salary Query: SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN( SELECT MAX(SAL) FROM EMPLOYEE) EMPLOYEE table name SALARY column name For Average Salary Query: SELECT AVG(SALARY) AS AVG_SAL FROM EMPLOYEE

Venkata Subbareddy · Answer

HI jayachandra, Highest Salary Query. SELECT TOP 1 column FROM YOUR_TABLE ORDER BY column DESC

Venkata Subbareddy · Answer

Hi JayaChandra, use Aggeative Functions in sql

second highest and avg salary

Answers (5)