SQL Server practical question with a possible solution: Top 3 Highest salary

 

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.

Solution No -4 : Using the "RANK" function:

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;

This query uses the "RANK" function to assign a rank to each employee based on their salary, with the highest salary being assigned a rank of 1. The outer query selects the first_name, last_name, and salary columns for employees whose salary rank is 1-3.