SQL Interview Questions

Nth Highest Salary


Create table #tblEmployee (Id int, [Name] varchar(100), City varchar(100), Salary decimal (10,3))
Insert into #tblEmployee values(1, 'Prakash Rathod', 'Gandhinagar', 100000)
Insert into #tblEmployee values(2, 'Salman Khan', 'Mumbai', 100000)
Insert into #tblEmployee values(3, 'Aamir Khan', 'Mumbai', 90000)
Insert into #tblEmployee values(4, 'Katrina Kaif', 'Mumbai', 80000)
Insert into #tblEmployee values(5, 'Rashmi Bansal', 'Indor', 95500)
Insert into #tblEmployee values(6, 'Jinen Kothari', 'Ahmedabad', 60000)
Insert into #tblEmployee values(7, 'Deepika Patel', 'Gandhinagar', 60000)
Insert into #tblEmployee values(8, 'Ranbir Singh', 'Gandhinagar', 36000)
Insert into #tblEmployee values(9, 'Mohini Trivedi', 'Ahmedabad', 25000)
Insert into #tblEmployee values(10, 'Jalpa Patel', 'Surat', 100000)
Insert into #tblEmployee values(11, 'Kruti Patel', 'Surat', 100000)
Insert into #tblEmployee values(12, 'Viral Zala', 'Surat', 100000)
Insert into #tblEmployee values(13, 'Dipbha Parmar', 'Surat', 100000)
Insert into #tblEmployee values(14, 'Jagubha Chotu', 'Gandhinagar', 100000)
Insert into #tblEmployee values(15, 'Kailashba Rathod', 'Surat', 100000)
select * from #tblEmployee

--2nd hightest salary
--SELECT top 1 Salary FROM #tblEmployee
--WHERE Salary < (select max(salary) from #tblEmployee ) ;With Result As ( select salary, DENSE_RANK() over (order by salary desc) as RowNumber from #tblEmployee ) select salary from result where RowNumber=1 drop table #tblEmployee