Postgresql Order by multiple columns from different tables

Question

I have the following tables:

Sources:

**id  | name**

 1      source1

 2      source2

 3      source3

 4      source4

Items:

**id | name | sourceId | created_at**

1    item3      3      2018-08-09 07:28:17

2    item2      2      2018-08-09 07:30:00

The sql:

SELECT
    sources.id,
    sources.name
FROM
     sources
LEFT JOIN
     items
         ON items.sourceId = sources.id
ORDER BY items.created_at DESC, sources.id

Expectation:

**id  | name**

2      source2

3      source3

1      source1

4      source4

Explanation:

I need a result containing all the sources from the sources table but ordered by most recent used sources(those assigned to items).Somehow the first results are the one from the sources table and then in the desc order the ones that can be found in the items table, like the following:

Real result:

**id  | name**

1      source1 

4      source4

2      source2

3      source3

I managed to get the desired result with my second sql but i think there is a solution for my first attempt too:

(SELECT
    sources.id,
    sources.name
FROM
     sources
INNER JOIN
     items
         ON items.sourceId = sources.id
ORDER BY items.created_at DESC, sources.id)
UNION ALL
(SELECT
       sources.id,
       sources.name
FROM
     sources
LEFT JOIN
    items
ON items.sourceId = sources.id
WHERE items.sourceId IS NULL)

Another problem with the above sql is that i don't quite understand why the order is messed up when i use UNION but the order is right when i use UNION ALL. As far as i know the only difference is that UNION eliminates duplicates while UNION ALL doesn't

Answer 1

If I got that right, NULLS LAST might do the trick.

...
ORDER BY items.created_at DESC
                          NULLS LAST,
         sources.id;