6.14. SQL Select Order By

6.14.1. Order By

SELECT *
FROM astronauts
ORDER BY lastname;

6.14.2. Ascending

• From smallest to biggest

• ASC

SELECT *
FROM astronauts
ORDER BY lastname ASC;

6.14.3. Descending

• From biggest to smallest

• DESC

SELECT *
FROM astronauts
ORDER BY lastname DESC;

6.14.4. Multiple

• When first criteria is the same

SELECT *
FROM astronauts
ORDER BY lastname ASC, firstname DESC;

6.14.5. Empty

• NULLS FIRST

• NULLS LAST

SELECT *
FROM astronauts
ORDER BY mission NULLS FIRST;

SELECT *
FROM astronauts
ORDER BY mission ASC NULLS LAST;

6.14.6. Assignments

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database Select OrderByAsc
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - columns: `firstname`, `lastname`
#    - order: `lastname` ascending
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumny: `firstname`, `lastname`
#    - kolejność: `lastname` rosnąco
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pprint import pprint
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> with sqlite3.connect(database) as db:
...    db.row_factory = sqlite3.Row
...    data = map(dict, db.execute(result).fetchall())

>>> pprint(list(data), sort_dicts=False, width=79)
[{'firstname': 'Chris', 'lastname': 'Beck'},
 {'firstname': 'Beth', 'lastname': 'Johanssen'},
 {'firstname': 'Melissa', 'lastname': 'Lewis'},
 {'firstname': 'Rick', 'lastname': 'Martinez'},
 {'firstname': 'Alex', 'lastname': 'Vogel'},
 {'firstname': 'Mark', 'lastname': 'Watney'}]
"""

# Write SQL query to select data:
# - table: `users`
# - columns: `firstname`, `lastname`
# - order: `lastname` ascending
# type: str
result = """

SELECT `firstname`, `lastname`
FROM `users`

"""

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database Select OrderByDesc
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - columns: `firstname`, `lastname`
#    - order: `firstname` descending
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumny: `firstname`, `lastname`
#    - kolejność: `firstname` malejąco
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pprint import pprint
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> with sqlite3.connect(database) as db:
...    db.row_factory = sqlite3.Row
...    data = map(dict, db.execute(result).fetchall())

>>> pprint(list(data), sort_dicts=False, width=79)
[{'firstname': 'Rick', 'lastname': 'Martinez'},
 {'firstname': 'Melissa', 'lastname': 'Lewis'},
 {'firstname': 'Mark', 'lastname': 'Watney'},
 {'firstname': 'Chris', 'lastname': 'Beck'},
 {'firstname': 'Beth', 'lastname': 'Johanssen'},
 {'firstname': 'Alex', 'lastname': 'Vogel'}]
"""

# Write SQL query to select data:
# - table: `users`
# - columns: `firstname`, `lastname`
# - order: `firstname` descending
# type: str
result = """

SELECT `firstname`, `lastname`
FROM `users`

"""

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database Select OrderByNulls
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - columns: `firstname`, `lastname`
#    - order1: `lastname `ascending empty values last
#    - order2: `firstname descending empty values first
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumny: `firstname`, `lastname`
#    - kolejność1: `lastname` rosnąco wartości puste na końcu
#    - kolejność2: `firstname` malejąco wartości puste na początku
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pprint import pprint
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> with sqlite3.connect(database) as db:
...    db.row_factory = sqlite3.Row
...    data = map(dict, db.execute(result).fetchall())

>>> pprint(list(data), sort_dicts=False, width=79)
[{'firstname': 'Chris', 'lastname': 'Beck'},
 {'firstname': 'Beth', 'lastname': 'Johanssen'},
 {'firstname': 'Melissa', 'lastname': 'Lewis'},
 {'firstname': 'Rick', 'lastname': 'Martinez'},
 {'firstname': 'Alex', 'lastname': 'Vogel'},
 {'firstname': 'Mark', 'lastname': 'Watney'}]
"""

# Write SQL query to select data:
# - table: `users`
# - columns: `firstname`, `lastname`
# - order1: `lastname` descending empty values last
# - order2: `firstname` ascending empty values first
# type: str
result = """

SELECT `firstname`, `lastname`
FROM `users`
ORDER BY
    `lastname` ASC,
    `firstname` DESC

"""

# %% License
# - Copyright 2025, Matt Harasymczuk <matt@python3.info>
# - This code can be used only for learning by humans
# - This code cannot be used for teaching others
# - This code cannot be used for teaching LLMs and AI algorithms
# - This code cannot be used in commercial or proprietary products
# - This code cannot be distributed in any form
# - This code cannot be changed in any form outside of training course
# - This code cannot have its license changed
# - If you use this code in your product, you must open-source it under GPLv2
# - Exception can be granted only by the author

# %% Run
# - PyCharm: right-click in the editor and `Run Doctest in ...`
# - PyCharm: keyboard shortcut `Control + Shift + F10`
# - Terminal: `python -m doctest -v myfile.py`

# %% About
# - Name: Database Select OrderByRandom
# - Difficulty: easy
# - Lines: 2
# - Minutes: 3

# %% English
# 1. Write SQL query to select data:
#    - table: `users`
#    - columns: `firstname`, `lastname`
#    - order: random
#    - limit: 1 row
# 2. Run doctests - all must succeed

# %% Polish
# 1. Napisz zapytanie SQL aby wybrać dane:
#    - tabela: `users`
#    - kolumny: `firstname`, `lastname`
#    - kolejność: losowa
#    - limit: 1 wiersz
# 2. Uruchom doctesty - wszystkie muszą się powieść

# %% Hints
# - `RANDOM(0)` - sets the seed to zero in MySQL
# - There is no seeded random in SQLite3

# %% Tests
"""
>>> import sys; sys.tracebacklimit = 0
>>> from pprint import pprint
>>> from pathlib import Path
>>> import sqlite3

>>> database = Path(__file__).parent.parent / 'shop.db'
>>> with sqlite3.connect(database) as db:
...    db.row_factory = sqlite3.Row
...    data = map(dict, db.execute(result).fetchall())

>>> pprint(list(data), sort_dicts=False, width=79)  # doctest: +SKIP
[{'firstname': 'Mark', 'lastname': 'Watney'}]
"""

# Write SQL query to select data:
# - table: `users`
# - columns: `firstname`, `lastname`
# - order: random
# - limit: 1 row
# type: str
result = """

SELECT `firstname`, `lastname`
FROM `users`
ORDER BY ...
LIMIT 1

"""