select 2nd max salary employees from Employee and salary table .
Here's an example of a SQL query to find the second largest salary from the Employee and Salary tables:
WITH cte AS (
SELECT Employee.EmployeeID, Employee.Name, Salary.Salary
FROM Employee
JOIN Salary
ON Employee.EmployeeID = Salary.EmployeeID
),
ranked AS (
SELECT EmployeeID, Name, Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn
FROM cte
)
SELECT EmployeeID, Name, Salary
FROM ranked
WHERE rn = 2;
In this example, the query first combines the data from the Employee and Salary tables using a join operation. The combined data is then used in a Common Table Expression (CTE) to create a derived table.
Next, the derived table is used in another query to add a row number column (rn) to the data, with the ROW_NUMBER() function and the ORDER BY clause. The row number column is used to rank the data based on the salary in descending order.
Finally, the ranked data is filtered to only return the second largest salary using the WHERE clause. The result will be the employee ID, name, and salary of the employee with the second largest salary.
No comments:
Add your comment