Each city and department once, unknown cities last

Remove repeats from pairs of columns, and decide where missing values go.

What to build

A query that lists every different pair of city and department.

Requirements

Return each different (city, dept) pair once.
Sort by city alphabetically, with unknown cities after all the known ones.
Within a city, sort by department.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  city TEXT
);

Output

Columns city and dept. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

city may be NULL, and dept is never NULL.

Example

When a test starts with:

INSERT INTO students VALUES (1, 'A', 'CSE', 'Dhaka'), (2, 'B', 'EEE', 'Dhaka'), (3, 'C', 'CSE', 'Dhaka'), (4, 'D', 'CSE', NULL), (5, 'E', 'BBA', 'Sylhet');

your SQL should print:

city    dept
------  ----
Dhaka   CSE
Dhaka   EEE
Sylhet  BBA
NULL    CSE
(4 rows)

Why it works

DISTINCT compares whole rows, and it treats two NULLs as the same. Sorting first by city IS NULL moves the unknown cities to the end in any DBMS, which NULLS LAST also does where it is supported.

Examples

Example 1 · Input

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  city TEXT
);
INSERT INTO students VALUES (1, 'A', 'CSE', 'Dhaka'), (2, 'B', 'EEE', 'Dhaka'), (3, 'C', 'CSE', 'Dhaka'), (4, 'D', 'CSE', NULL), (5, 'E', 'BBA', 'Sylhet');

Expected output

city    dept
------  ----
Dhaka   CSE
Dhaka   EEE
Sylhet  BBA
NULL    CSE
(4 rows)

Example 2 · Input

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  city TEXT
);
INSERT INTO students VALUES (1, 'A', 'CSE', NULL), (2, 'B', 'CSE', NULL);

Expected output

city  dept
----  ----
NULL  CSE
(1 row)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Hint 1

`SELECT DISTINCT city, dept` removes rows where both columns repeat.

Hint 2

SQLite sorts NULL first. `ORDER BY city IS NULL, city, dept` sorts the known cities first, because `city IS NULL` is 0 for them and 1 for NULL.

Solution

Show one correct solution (try it yourself first)

SELECT DISTINCT city, dept
FROM students
ORDER BY city IS NULL, city, dept;

Lesson: Sorting, limiting and DISTINCT →