CODEWITHSUNDEEP
sql
Vijay
Vijay

Reputation: 71

How to get order by with inner query

select 
    * 
from emp 
where id 
    (select order
    from department
    where name = 'testing'
    order by order asc)

I am getting ordered data from the inner query and with the id's I get I should get the result from the emp table to be in the same order as the inner query.

Upvotes: 0

Views: 13674

Answers (5)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

If it's reasonable to re-write your query as a join:

select e.*
from emp e
inner join department d
    on e.id = d.order
where d.name = 'testing'
order by d.order asc

Where this would change your results is if there are multiple rows in department with the same order value matching one or more rows in emp - in which case this query will return multiple rows, whereas the original would not. But if such a condition doesn't apply, then this is the query I'd recommend.

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44356

This will give the right number of rows in case there more than 1 match between emp.id and department.order

select * from emp e
where exists
(select 1 from department d where d.name = 'testing' 
and e.id = d.order1) -- order is a reserved word in several sql languages
order by id

It seems there is something funny going on between your tables. Would would department contain any information about emp(I assume it is employee table) ?

Upvotes: 1

senthilkumar2185
senthilkumar2185

Reputation: 2578

select e.* from emp as e, department d where e.id=d.order and d.name='testing' order by d.order

Upvotes: 0

Vikash Singh
Vikash Singh

Reputation: 814

Damien's answer is quite cool and perfect. but if you still want to go with subquery then try this

select * 
from emp 
where id in (select order from department where name = 'testing' order by order asc) 
order by id asc

May this help you

Upvotes: 1

Lorenzo Gatti
Lorenzo Gatti

Reputation: 1270

There is no guarantee that there is an actual temporary table with the inner query and that it is sorted and processed in a certain way. However, you can sort the outer query by emp.id.

select * from emp where id in 
     (select order from department where name = 'testing')
order by id asc

Upvotes: 0

Related Questions