What is the correct usage of IN and ORDER BY keywords when used in conjunction with subqueries?

All we need is an easy explanation of the problem, so here it is.

ERD for my simple db design:
What is the correct usage of IN and ORDER BY keywords when used in conjunction with subqueries?

I want to order children based on their parent’s last name. I am trying to use the following query:

SELECT * 
FROM children 
WHERE parent_id IN (SELECT parent_id  
                    FROM parent 
                    ORDER BY lastName ASC);

SELECT parent_id FROM parent ORDER BY lastName ASC returns the parent_ids in the order of the parent’s last names (this is the intended behavior). The ordering of parent_ids according to their last names in ascending order is 154,156,155,157

However, when used as a subquery, the ordering is apparently not used correctly. The results are:
What is the correct usage of IN and ORDER BY keywords when used in conjunction with subqueries?

As you can see the results are ordered in ascending order of parent_id (not of the child’s respective parent’s last name).

Is there a way to correctly structure this query without changing my design?

Thank you.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The subselect returns a set, which has no order, the order by there is useless – it will not apply to the final result set. If you want to order "children" by their "parent"s’ "last names", you’ll have to project those "last names" to the outer select by joining the two relations:

SELECT c.* 
FROM children c
INNER JOIN parent p
ON c.parent_id = p.parent_id
ORDER BY lastName ASC

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply