To find the second highest salary from an employee table in SQL, you can use various methods. Here are a few common approaches:
Method 1: Using DISTINCT and ORDER BY
You can select distinct salaries, order them in descending order, and then use LIMIT to get the second highest:
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
Method 2: Using a Subquery
You can use a subquery to find the maximum salary that is less than the maximum salary in the table:
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Method 3: Using ROW_NUMBER()
If your SQL dialect supports window functions, you can use ROW_NUMBER():
SELECT salary
FROM (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees
) AS ranked_salaries
WHERE rank = 2;
Notes:
- Ensure that you handle the case when there may be duplicate salaries. The DISTINCT keyword in Method 1 and the subquery in Method 2 handle this automatically.
- The SQL syntax might slightly differ based on the database you are using (MySQL, PostgreSQL, SQL Server, etc.), so make sure to adjust the queries based on your specific SQL environment.
Choose any of the above methods based on your requirements and the capabilities of your SQL database!