How to get order by with inner query

Question

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.

I think you've dropped an `in` between `id` and `(`. This would be better done as an inner join rather than a subquery - would that be a reasonable change? The only way to deterministically determine the order of the results from a query is to have an `ORDER BY` on the outermost `SELECT` statement. — Damien_The_Unbeliever, Sep 30 '13 at 08:45
Does this answer your question? [Is order by clause allowed in a subquery](https://stackoverflow.com/questions/2101908/is-order-by-clause-allowed-in-a-subquery) — philipxy, Apr 06 '23 at 06:09

Damien_The_Unbeliever · Answer 1 · 2013-09-30T09:30:24.993

2

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.

edited Sep 30 '13 at 09:30

answered Sep 30 '13 at 08:46

Damien_The_Unbeliever

234,701
27
340
448

Sorry for my ignorance, I don't have any relationship set between dept and emp tables. will this work in this case?? @Damien_The_Unbeliever – Vijay Sep 30 '13 at 08:54
1

@Vijay - yes, that would be fine. SQL allows you to write arbitrary join conditions between tables - you don't have to only follow defined foreign keys. – Damien_The_Unbeliever Sep 30 '13 at 08:56
You miss `where` clause, but answer is pretty OK – veljasije Sep 30 '13 at 08:58
@veljasije - what `where` clause? - the only `where` clause in the original question isn't required because it's covered by the `ON` for the `JOIN` – Damien_The_Unbeliever Sep 30 '13 at 09:02
I mean `Where name = 'testing'` – veljasije Sep 30 '13 at 09:23
1

@veljasije - ah, yes. sorry. Added. – Damien_The_Unbeliever Sep 30 '13 at 09:30

score 1 · Answer 2 · answered Sep 30 '13 at 08:53

1

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

answered Sep 30 '13 at 08:53

Vikash Singh

804
1
10
11

Thanks!! vikash-singh but i want order to be in the order column of department not based on the emp id. is that acheivable?. – Vijay Sep 30 '13 at 08:59
then use **group by id** in place of *order by id*. – Vikash Singh Sep 30 '13 at 09:01

score 1 · Answer 3 · answered Sep 30 '13 at 09:05

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) ?

score 0 · Answer 4 · answered Sep 30 '13 at 08:51

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

score 0 · Answer 5 · answered Sep 30 '13 at 08:53

0

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

answered Sep 30 '13 at 08:53

senthilkumar2185

2,536
3
22
36

How to get order by with inner query

5 Answers5