--

CREATE TABLE EmployeeMaxSalary
(
	 EmpId INT PRIMARY KEY
    ,EmpName VARCHAR(1)
	,EmpSalary INT	
)
GO

INSERT INTO EmployeeMaxSalary
SELECT DISTINCT Number , CHAR(Number) , Number * 10 / 2
FROM 
	MASTER..SPT_VALUES
WHERE number > 0 AND number < 100


CREATE NONCLUSTERED INDEX Ix_EmpSalary ON EmployeeMaxSalary(EmpSalary)
INCLUDE ( EmpName )

--

--

--METHOD 1 | Using Aggregate Function

SELECT EmpId, EmpName, EmpSalary FROM EmployeeMaxSalary 
WHERE EmpSalary = ( 
SELECT Max(EmpSalary) FROM EmployeeMaxSalary
 WHERE EmpSalary < ( SELECT Max(EmpSalary) FROM EmployeeMaxSalary ) )

--

--


--METHOD 2 | Using TOP WITH TIES

SELECT TOP 1 WITH TIES EmpId , EmpName , EmpSalary  FROM 
	( SELECT TOP 2 WITH TIES EmpId , EmpSalary ,EmpName FROM EmployeeMaxSalary ORDER BY EmpSalary DESC ) a
ORDER BY EmpSalary


--

--

--METHOD 3 | Using Ranking Functions

SELECT EmpId, EmpName, EmpSalary
FROM 
(
    SELECT EmpId, EmpName, EmpSalary , ROW_NUMBER() OVER (ORDER BY EmpSalary DESC) rnk
    from EmployeeMaxSalary 
) X
WHERE rnk = 2

--

--

--METHOD 4 | Using Correlated Query

SELECT EmpId, EmpName, EmpSalary FROM EmployeeMaxSalary E1
WHERE 1 = ( SELECT DISTINCT COUNT(*) FROM EmployeeMaxSalary E2 WHERE E2.EmpSalary > E1.EmpSalary )


--

--

--METHOD 5 | Using OffSet

SELECT EmpId, EmpName, EmpSalary
FROM EmployeeMaxSalary 
ORDER BY EmpSalary DESC
OFFSET (1) ROWS FETCH NEXT 1 ROWS ONLY;

--