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

by

The question:

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.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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


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 Comment