Write a SQL Query to find the nth highest salary?

 Please find the table details :










Case 1: Find Second Highest salary

select top 1 salary from (select  top 2 salary from [dbo].[new_employees] order by salary desc) result order by salary



Case 2: Finding 3rd highest salary using CTE in SQL Server.

With  RESULT  AS (

select   salary , dense_rank() over ( order by salary desc) as salary_rank from [dbo].[new_employees] 

)

select top 1 Salary from RESULT where RESULT.salary_rank=3


Uploading: 247171 of 247171 bytes uploaded.



Comments

Post a Comment

Popular posts from this blog

Write a query to practice in Northwind database

  1. WAQ to display average price for all beverages products?   select AVG(isnull(p.unitprice,0)) as "avg product price" from dbo.Products P           join dbo.Suppliers s on s.SupplierID=P.SupplierID   join dbo.Categories c on c.CategoryID=p.CategoryID   where CategoryName='Beverages' 2. WAQ to display unique titles in the employee table?         SELECT distinct e.Title  FROM [dbo].[Employees] AS  e 3. WAQ to display Total Sales for each customer in October 1996 (based on OrderDate).       Show the result in CustomerID, CompanyName, and total sales, sorted in total sales in Decending order      select * from [dbo].[Customers] where CustomerID='SPLIR' select * from [dbo].[Orders] where CustomerID='SPLIR' select * from [dbo].[Order Details] where orderID in ( select orderID from [dbo].[Orders] where CustomerID='SPLIR') select cm.CustomerID,cm.Compan...
Read more

How to write an email for last day in office

  Subject: Farewell and Thank You Dear [colleagues], As my last day in the office approaches, I wanted to take a moment to express my gratitude for the time we have spent working together. It has been an honor to be a part of such a dedicated and talented team, and I have learned so much during my time here. I have cherished the opportunity to collaborate with each and every one of you, and I am grateful for the friendships that have been formed. Your guidance and support have been invaluable, and I will always remember the positive impact that this organization and each of you have had on my professional development. I want to thank you for your hard work and dedication, and for making this an enjoyable and fulfilling experience. I wish you all the best in your future endeavors and hope to stay in touch. Please do not hesitate to reach out to me in the future. Sincerely, [Your Name] ---------------------------------------------------------------------------------------------------...
Read more