Here there are diffrent methods are used to find the second Highest salary, max salary, min salary from the Employee table in MS-SQL Server.
use EmpDB;
select DISTINCT salary from Employee group by salary ;
// Show all the Distinct salaries
SELECT Min(Salary ) FROM [EmpDB].[dbo].[Employee]
WHERE Salary IN (SELECT DISTINCT TOP 2 Salary FROM [EmpDB].[dbo].[Employee] order BY Salary desc) // for 2nd highest salary( i.e. Top 2 is used), you can change the number as per to get the desired one.
SELECT MAX(Salary) FROM Employee WHERE Salary<(SELECT MAX(Salary) FROM Employee) // only for 2nd highest salary
select distinct a.salary from Employee a where 2=( select count(distinct b.salary) from Employee b where a.salary<=b.salary) // for 2nd highest salary( i.e. where 2= is used), you can change the number(3,4,5..) as per your need to get the desired one.
SELECT * FROM (SELECT salary, ROW_NUMBER() OVER (order by Employee.Salary DESC) AS RANK FROM Employee group by salary) v where RANK = 8 ;
/*---Finding Max and min salary from Emp Table*/
SELECT a.[firstName], a.Salary AS Salary
FROM Employee AS a
WHERE a.Salary IN (
SELECT MIN(b.Salary)
FROM Employee AS b)
UNION
SELECT a.[firstName], a.Salary AS Salary
FROM Employee AS a
WHERE a.Salary IN (
SELECT MAX(b.Salary)
FROM Employee AS b)
ORDER BY a.Salary
/*Selecting all the 2nd max salary here 2 represents the 2nd higest salary
Here u can able to sell all the records with second highest salary
Table:
CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1,'Vamshi', 1000)
INSERT INTO #T1 VALUES (2, 'xxxxx', 2000)
INSERT INTO #T1 VALUES (3, 'yyyyy', 3000)
INSERT INTO #T1 VALUES (4, 'zzzzz', 4000)
INSERT INTO #T1 VALUES (5, 'sssss', 5000)
INSERT INTO #T1 VALUES (6, 'ccccc', 6000)
INSERT INTO #T1 VALUES (7, 'ppppp', 2000)
INSERT INTO #T1 VALUES (8, 'aaaaa', 4000)
INSERT INTO #T1 VALUES (9, 'bbbbb', 5000)
INSERT INTO #T1 VALUES (10,'eeeee', 5000)
Select * from #t1 order by salary;
SELECT a.ID1, a.[Name], a.Salary
FROM #T1 AS a
WHERE (2) = (
SELECT COUNT(DISTINCT(b.Salary))
FROM #T1 AS b
WHERE b.Salary > a.Salary)
*/
/* Displaying all the records with max and min salary
select distinct * from Employee where salary in (select max(salary) from Employee
union
select min(salary) from Employee
);*/
/* finding all the 2nd higest Salary using Rank here 2 refers to the 2nd highest salary
, here a means alias
Select *
from (Select *, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Ranks from Employee) a
WHERE Ranks = 2
*/
/*
first method:
select top 1 salary from (
select top 6 salary from employee group by salary order by salary desc) a order by salary
go
2nd method:
with nthsalary as
(
select salary,row_number() over(order by salary desc) as row from employee group by salary
)
select salary from nthsalary where row=6
GO
3RD method:
select distinct * from employee a where 6=(select count(distinct salary)
from employee b where a.salary<=b.salary)
*/
/*
create table EmpDept(
empno int identity(1,1),
empname varchar(50),
sal numeric(18,2),
dep varchar(20)
)
go
insert into EmpDept(empname, sal, dep)
select 'A_123',12000,'BANK'
Union
select 'A_234',5000,'BANK'
Union
select 'A_345',10000,'BANK'
Union
select 'A_456',25000,'BANK'
Union
select 'A_567',8000,'BANK'
Union
select 'B_123',25000,'PROG'
Union
select 'B_234',27000,'PROG'
Union
select 'B_345',23000,'PROG'
Union
select 'B_456',13000,'PROG'
Union
select 'B_567',50000,'PROG'
Union
select 'C_123',11000,'TEST'
Union
select 'C_234',9000,'TEST'
Union
select 'C_345',22000,'TEST'
Union
select 'C_456',30000,'TEST'
Union
select 'C_567',8000,'TEST'
Select * from EmpDept
Select * from EmpDept a where empno in (select top 3 empno from EmpDept where dep=a.dep order by sal desc)*/
