Number Of different ways to find highest or maximum salary of a employee

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

 

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s