SQL Server Query related questions

1:- Finding nth highest salary in SQL?

Below is the Table structure:

Create table Employees

(

     ID int primary key identity,

     FirstName nvarchar(50),

     LastName nvarchar(50),

     Gender nvarchar(50),

     Salary int

)

GO

Insert into Employees values ('Suraj', 'Kumar', 'Male', 70000)

Insert into Employees values ('Rahul', 'Mad', 'Male', 60000)

Insert into Employees values ('Steve', 'jobs', 'Male', 46000)

Insert into Employees values ('Mahesh', 'kumar', 'Male', 70000)

Insert into Employees values ('Suresh', 'sinha', 'Male', 45000)

Insert into Employees values ('Ravi', 'kukreja', 'Female', 30000)

Insert into Employees values ('Priyanka', 'singh', 'Female', 36000)

Insert into Employees values ('Riya', 'Mishra', 'Male', 90000)

GO

Here we are finding 2nd highest Salary from Employee Table.

Using  Sub-Query with Max function

  Select Max(Salary) from Employee where Salary < (Select Max(Salary) from Employee)

Using  Sub-Query 

  SELECT TOP 1 SALARY

  FROM (

        SELECT DISTINCT TOP 2 SALARY

        FROM EMPLOYEE

        ORDER BY SALARY DESC

        ) RESULT

  ORDER BY SALARY ASC;

Using CTE

  WITH RESULT AS

  (

      SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK, SALARY

      FROM EMPLOYEES

  )

  SELECT TOP 1 SALARY

  FROM RESULT

  WHERE DENSERANK = 2

2:- Delete duplicate rows from table

WITH tblTemp as

(

   SELECT ROW_NUMBER() Over(PARTITION BY Name, Department ORDER BY Name)

   AS RowNumber, * FROM emp

)

DELETE FROM tblTemp where RowNumber >1