I have to do the following exercise:

Write a SQL query to list the names of all people who starred in a movie released in 2004, ordered by birth year. Your query should output a table with a single column for the name of each person. People with the same birth year may be listed in any order. No need to worry about people who have no birth year listed, so long as those who do have a birth year are listed in order. If a person appeared in more than one movie in 2004, they should only appear in your results once.

I have the following schema in my movies.db database:

CREATE TABLE movies (
                    id INTEGER,
                    title TEXT NOT NULL,
                    year NUMERIC,
                    PRIMARY KEY(id)
                );
CREATE TABLE stars (
                movie_id INTEGER NOT NULL,
                person_id INTEGER NOT NULL,
                FOREIGN KEY(movie_id) REFERENCES movies(id),
                FOREIGN KEY(person_id) REFERENCES people(id)
            );
CREATE TABLE directors (
                movie_id INTEGER NOT NULL,
                person_id INTEGER NOT NULL,
                FOREIGN KEY(movie_id) REFERENCES movies(id),
                FOREIGN KEY(person_id) REFERENCES people(id)
            );
CREATE TABLE ratings (
                movie_id INTEGER NOT NULL,
                rating REAL NOT NULL,
                votes INTEGER NOT NULL,
                FOREIGN KEY(movie_id) REFERENCES movies(id)
            );
CREATE TABLE people (
                id INTEGER,
                name TEXT NOT NULL,
                birth NUMERIC,
                PRIMARY KEY(id)
            );

When I run this query:

SELECT DISTINCT name, birth
FROM people
WHERE id IN (    
    SELECT person_id
    FROM stars
    WHERE movie_id IN (
        SELECT id
        FROM movies
        WHERE year = 2004
    )
)
ORDER BY birth;

I get a column filled with NULL values:

However, if I leave out the line ORDER BY birth, that column shows the values correctly (although unsorted). What should I do to solve this issue ?

CodePudding user response：

When sorted normally, the NULL's are considered as lowest values.
So when you order ascending (the default) then the NULL's come first.

But the field birth is a numeric type.
So sorting the negative value DESCENDING will push those pesky nullies down.

SELECT DISTINCT name, birth
FROM people
WHERE id IN (    
    SELECT person_id
    FROM stars
    WHERE movie_id IN (
        SELECT id
        FROM movies
        WHERE year = 2004
    )
)
ORDER BY -birth DESC;

Simplified test

create table people (id integer primary key, birth int)

insert into people (birth) values (100),(42),(16),(null)

select * from people
order by -birth desc

id | birth
-: | ----:
 3 |    16
 2 |    42
 1 |   100
 4 |  null

db<>fiddle here

And another way is to change the null's to an impossible high value.

ORDER BY COALESCE(birth, 999)

Or use the newer ASC NULLS LAST syntax.

ORDER BY birth ASC NULLS LAST