Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value

You can change and use it for getting nth highest salary from Employee table as follows
SELECT TOP 1 salary FROM (SELECT DISTINCT TOP n salaryFROM employee ORDER BY salary DESC) aORDER BY salary
where n > 1 (n is always greater than one)


with cte as
(
select salary ,ROW_NUMBER() over (order by salary desc) as 'rownum'
from employee 
)

select salary from cte where rownum = n  (n is always greater than one)

Count Duplicate Records – Rows

SELECT YourColumn, COUNT(*) TotalCount FROM YourTable GROUP BY YourColumn HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC
For Delete
------
delete 
FROM YourTable
WHERE ID NOT IN (SELECT MAX(ID) FROM YourTable GROUP BY YourColumn) and YourColumn=0 

delete

FROM Exam_Backup

WHERE ID NOT IN (SELECT MAX(ID) FROM Exam_Backup GROUP BY QuestionNo) and optionName=0

WITH CTE

WITH CTE (last_name,first_name,dcount) 

AS

(

SELECT last_name,first_name ,

ROW_NUMBER() OVER (PARTITION BY last_name,first_name ORDER BY last_name) AS dcount

FROM tbl_member 

)

SELECT * FROM CTE WHERE dcount > 1