Order by only that part which is coming from left join query

Question

I have two tables 1. Person and 2. Children

CREATE TABLE Persons (
PersonID int,
Name varchar(255)
);

CREATE TABLE Children (
ChildID int,
PersonID int,
Name varchar(255),
DOB Date);

I combined them with left join.

select 
person.PersonID
, person.Name
, child.childID
, Child.DOB
FROM(
select PersonID
, Name
From Persons
) as person

left join (
select ChildID
, PersonID
, Name
, DOB
From Children
Order By DOB ASC
) as child
on child.PersonID = person.PersonID

The result show how children a person has with their date of birth. Till then this is fine. What I want Person will be ordered by ascending with their ID and children list will be ordered by their date of birth in ascending order. But it seems that children are coming in random order. I tried to order by on the result but that does not serve my purpose. I want PersonID ascending order and children list of each person will be ordered by their date of birth ascending.

Here is the link http://sqlfiddle.com/#!9/b56f95/3 of the table and query I performed.

Tables have no order, result sets do. ORDER BY has no meaning in a subselect without LIMIT. This is a faq. — philipxy, Oct 25 '17 at 18:35
In a select statement the sort clause happens before a select clause. So sort in the outermost select statement, but you don't have to select those columns. — philipxy, Oct 25 '17 at 18:49
Good edits. Please also put input, output & desired output in your question. This is all explained in [ask] and [mcve]. PS For the future before & hopefully instead of asking google many clear, concise, specific phrasings of your question/problem/desiderata and read many hits. If you ask use one as title. Because as I say this is a faq & duplicates should get blocked from answers. — philipxy, Oct 25 '17 at 19:09
Possible duplicate of [how to sort order of LEFT JOIN in SQL query?](https://stackoverflow.com/questions/5355585/how-to-sort-order-of-left-join-in-sql-query) — philipxy, Oct 25 '17 at 19:34

Flynnstone · Accepted Answer · 2017-10-25T19:10:40.237

The query you have is not explicitly ordered, in this case it appears that they are ordered by the PersonID but that isn't always guaranteed. The ordering of the subquery will also not change the ordering of the outer query.

If you move the order by from the subquery into the outer query the output will be ordered correctly:

select 
  person.PersonID
  , person.Name
  , child.childID
  , Child.DOB
FROM(
  select PersonID
  , Name
  From Persons
) as person

left join (
  select ChildID
  , PersonID
  , Name
  , DOB
  From Children
) as child
on child.PersonID = person.PersonID
order by child.DOB

If you wanted to each parent's children listed together for example:

Parent 1, Child 1
Parent 1, Child 2
Parent 2, Child 1
Parent 3, Child 1
Parent 3, child 2

You can add an order by PersonID also:

select 
  person.PersonID
  , person.Name
  , child.childID
  , Child.DOB
FROM(
  select PersonID
  , Name
  From Persons
) as person

left join (
  select ChildID
  , PersonID
  , Name
  , DOB
  From Children
  Order By DOB ASC
) as child
on child.PersonID = person.PersonID
order by person.PersonID, child.DOB

This will first order the result by the PersonID from the persons table and then order each parents children by their DOB

Jeff Standen · Answer 2 · 2017-10-25T19:32:12.617

The JOIN doesn't preserve your row ordering, even if you explicit sort it. You need to do the sorting afterwards:

SELECT 
  person.PersonID
  , person.Name
  , child.childID
  , Child.DOB
FROM Persons AS person
LEFT JOIN Children AS child
ON child.PersonID = person.PersonID
ORDER BY PersonID, DOB

I removed the ORDER BY in your JOIN and added one to the end of the outer query.

I also optimized out the subquery in your SELECT and JOIN since you can just use FROM Persons AS person and LEFT JOIN Children AS child to accomplish the same thing.

score 0 · Answer 3 · answered Oct 25 '17 at 19:17

You can simplify your query and order the result by the two columns:

select 
person.PersonID
, person.Name
, child.childID
, Child.DOB
From Persons as person
left join Children as child
on child.PersonID = person.PersonID
Order By PersonID, DOB

Logically, ORDER BY is applied after the result of the query is created. In this case it is a simple ORDER BY, first for the ID, then by DOB.

Order by only that part which is coming from left join query

3 Answers3