Suppose you have a table named "employees" with the
following columns: employee_id (int), first_name (varchar), last_name
(varchar), hire_date (datetime), and salary (decimal). Write an SQL query to
retrieve the top 3 highest-paid employees in the table.
Solution 1:
You can use the "TOP" and "ORDER BY" clauses to retrieve
the highest-paid employees. Here's an example query:
SELECT TOP 3 first_name, last_name, salary
FROM employees
ORDER BY salary DESC;
This query selects the first_name, last_name, and salary columns
from the "employees" table, and then sorts the results by the
"salary" column in descending order. The "TOP 3" clause
limits the results to the top 3 rows.
Note: If
there are ties for the fifth highest salary, the query will return all
employees with that salary. If you want to limit the results to exactly 3 rows,
you can use the "SELECT DISTINCT TOP 3" syntax instead.
there are other ways to write the query to retrieve the top 3 highest-paid employees from the "employees" table in SQL Server. Here are a few alternative solutions:
Solution 2: Using Limit Clause:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
Note : "LIMIT" clause is not supported in SQL Server, but it is supported in some other database management systems such as MySQL and PostgreSQL.
Solution 3: Using Sub-query:
SELECT first_name, last_name, salary FROM employees WHERE salary IN ( SELECT TOP 3 salary FROM employees ORDER BY salary DESC );
This query uses a subquery to retrieve the top 3 highest salaries, and then selects the first_name, last_name, and salary columns for the employees with those salaries. The "WHERE salary IN" clause filters the results to only include employees whose salary is in the top 3.
SELECT first_name, last_name, salary FROM ( SELECT first_name, last_name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees ) AS ranked_employees WHERE salary_rank <= 3;
