I am trying to fetch record from empMaster table for the nth highest Salary.But getting error near 'N'

Question

I am trying to fetch record from empMaster table for the nth highest Salary.But getting error near 'N' .

SELECT TOP 1 salary
FROM 
    (SELECT DISTINCT TOP n salary 
     FROM empMaster
     ORDER BY salary DESC) AS temp
ORDER BY salary

Error message is:

Incorrect syntax near 'n'.

n have to be realistic number not variable – hoangnh Mar 20 '20 at 04:34 — hoangnh, Mar 20 '20 at 04:34

score 1 · Answer 1 · answered Mar 20 '20 at 04:39

1

You can write query in SQL Server as given below:

DECLARE @n INT = 5
SELECT salary AS [nthSalary]
FROM
(
SELECT salary, ROW_NUMBER() OVER(ORDER BY salary DESC) AS rownum 
FROM empMaster) AS t
WHERE rownum = @n

answered Mar 20 '20 at 04:39

Venkataraman R

12,181
2
31
58

Thank you..its working. But can you please tell me what this " INT=5 " indicates? – PoojaP Mar 20 '20 at 04:57
I just mentioned n= 5 as an example. You can mention any specific number you want for getting the nth salary. – Venkataraman R Mar 20 '20 at 05:01
if it works, accept the answer. so, it is helpful for others in future. – Venkataraman R Mar 20 '20 at 06:15

score 0 · Answer 2 · answered Mar 20 '20 at 07:29

SELECT TOP 1 Salary FROM (SELECT DISTINCT TOP N Salary FROM dbo.Employee ORDER BY Salary DESC) AS temp ORDER BY Salary

//* enter No of Highest salary in N Like 'N'=7 ( means 7th Highest salary) //*Incorrect syntax near 'n'. enter Numeric digit instead of 'n'

Yogesh Sharma · Answer 3 · 2020-03-20T08:48:06.360

N will have some numeric value. So, use TOP (5) or TOP (@Nvariable).

You can use dense_rank to find highest salary employees :

select emp.*
from (select emp.*, 
             dense_rank() over (order by sal desc) as seq
      from empmaster emp
     ) emp
where emp.seq <= 5; -- Pass variable or change Nth value here

Dense_rank() will give you ties employees, meaning that if one or more employees have same salary then it will give you all.

I am trying to fetch record from empMaster table for the nth highest Salary.But getting error near 'N'

3 Answers3