Recent days i heard this is the common question that is asking in interview so i am giving some way that will help you to found the maximum salary in sql.

Create table Employee

(Salary bigint,

name nvarchar(max)

)

insert into Employee values(100,’abc1′)

insert into Employee values(103,’abc2′)

insert into Employee values(1000,’abc3′)

insert into Employee values(100,’abc4′)

insert into Employee values(1030,’abc5′)

insert into Employee values(1002,’abc6′)

insert into Employee values(1001,’abc7′)

insert into Employee values(100,’abc8′)

insert into Employee values(10045,’abc9′)

select * from Employee order by Salary desc

way 1:

select top 1 salary from (

select distinct top 3 Salary from Employee order by salary desc) a order by Salary asc

way 2:

select salary from Employee e1 where 2 =(select count(distinct(Salary)) from Employee e2 where e2.Salary>e1.Salary)

way 3:

select salary from (

select salary,ROW_NUMBER() over(order by salary desc)as vaibhav from Employee)a1 where vaibhav =3

I am giving alternate query also

- SELECT *

FROM one one1

WHERE ( 3 ) = ( SELECT COUNT( one2.salary )

FROM one one2

WHERE one2.salary >= one1.salary

)

2. select salary from one order by salary desc limit 3,1

3. SELECT salary

FROM

(

SELECT @rn := @rn + 1 rn,

a.salary

FROM tableName a, (SELECT @rn := 0) b

ORDER BY salary DESC

) sub

WHERE sub.rn = 3

4. SELECT TOP 1 Salary

FROM (

SELECT DISTINCT TOP N Salary

FROM Employee

ORDER BY Salary DESC

) AS Emp

ORDER BY Salary

5. select * from (

select Emp.*,

row_number() over (order by Salary DESC) rownumb

from Employee Emp

)

where rownumb = n; /*n is nth highest salary*/

6. WITH CTE AS

(

SELECT EmpID,EmpName,EmpSalar,

RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)

FROM dbo.Salary

)

SELECT EmpID,EmpName,EmpSalar

FROM CTE

WHERE RN = @NthRow

7. SELECT TOP 1 salary FROM (

SELECT TOP 3 salary

FROM employees

ORDER BY salary DESC) AS emp

ORDER BY salary ASC

Row Number :

8. SELECT Salary,EmpName

FROM

(

SELECT Salary,EmpName,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum

FROM EMPLOYEE

) As A

WHERE A.RowNum IN (2,3)

Sub Query :

9. SELECT *

FROM Employee Emp1

WHERE (N-1) = (

SELECT COUNT(DISTINCT(Emp2.Salary))

FROM Employee Emp2

WHERE Emp2.Salary > Emp1.Salary

)

Top Keyword :

10. SELECT TOP 1 salary

FROM (

SELECT DISTINCT TOP n salary

FROM employee

ORDER BY salary DESC

) a

ORDER BY salary