How to write SQL query to find max and min salary along with employee name in Oracle

I have one employee table. Please find below the table structure along with data.

I want to find the maximum salary and minimum salary along with employee name.

expected output will be:

To find out the maximum salary along with employee name I have written this query:

SELECT name,salary FROM  employee where salary
= (select max(salary) from employee);

And to find out the minimum salary along with employee name I have written this query:

SELECT name,salary FROM  employee where salary
= (select min(salary) from employee);

But I am unable to merge these two queries.

Can someone please guide me to build the SQL query which will return maximum salary and minimum salary along with employee name?

Answer

Here is one way, using RANK. This answer makes one pass in a CTE to find the lowest and highest ranked salaries in the employee table. Then, it subqueries only once to retrieve the full matching records.

WITH cte AS (
    SELECT NAME, SALARY,
        RANK() OVER (ORDER BY SALARY) rnk_min,
        RANK() OVER (ORDER BY SALARY DESC) rnk_max
    FROM employee
)

SELECT NAME, SALARY
FROM cte
WHERE rnk_min = 1 OR rnk_max = 1
ORDER BY SALARY;

Demo