CODEWITHSUNDEEP
sqloracle-databasesubquerysql-order-by
ashur
ashur

Reputation: 4317

Order by subquery

I have the following oracle SQL code, but I can't understand what is the purpose of ordering by a subquery. Anyone can explain it clearly to me ?

SELECT employee_id, last_name
  FROM employees e
 ORDER BY (
            SELECT department_name
            FROM departments d
            WHERE e.department_id = d.department_id
           );

Upvotes: 2

Views: 115

Answers (2)

dotnetom
dotnetom

Reputation: 24901

The ordering is done by results from other table. In this case the query returns only results from employees table, but the ordering is done by department_name, which is stored in departments table.

You could achieve identical result by using join, selecting only values from employees table, and ordering by department_name from departments table:

SELECT e.employee_id, e.last_name
FROM employees e INNER JOIN departments d
    ON e.department_id = d.department_id
ORDER BY d.department_name

This query is valid if employee must always have a department. If there can be employees without departments then you should use LEFT join instead.

Upvotes: 4

venkat
venkat

Reputation: 523

The clear intention of that query is employee_id and last_name from employees should be order by department_name from departments.

Okay, you don't subquery then go for join 

select e.employee_id,e.last_name from employees e join departments d on
e.department_id = d.department_id order by d.department_name;

Upvotes: 1

Related Questions