ORDER BY w SQLite: sortowanie ASC, DESC i po wielu kolumnach

Bez ORDER BY kolejność wierszy jest nieokreślona

SELECT bez ORDER BY zwraca wiersze w takiej kolejności, jaka akurat jest wygodna dla SQLite. Na małej tabeli często wygląda to jak kolejność wstawiania, co usypia czujność. Nie ufaj temu. Wystarczy, że zostanie użyty indeks, tabela urośnie albo zmieni się plan zapytania, a kolejność może się zmienić bez ostrzeżenia.

Jeśli zależy ci na kolejności wierszy, powiedz to wprost:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT,
    signup_date TEXT
);

INSERT INTO users (name, country, signup_date) VALUES
    ('Boris', 'FR', '2024-03-12'),
    ('Ada',   'US', '2024-01-05'),
    ('Rosa',  'FR', '2024-02-20'),
    ('Chen',  'US', '2024-03-01');

SELECT name, country FROM users ORDER BY name;

ORDER BY name domyślnie sortuje rosnąco. Wynik to Ada, Boris, Chen, Rosa: alfabetycznie, za każdym razem, niezależnie od tego, jak tabela jest zapisana na dysku.

ASC i DESC

ASC oznacza sortowanie rosnące (od najmniejszego do największego, od A do Z, od najstarszego do najnowszego). DESC oznacza malejące, czyli odwrotne. ASC jest domyślne, więc prawie zawsze się je pomija:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   '2024-01-05'),
    ('Rosa',  '2024-02-20'),
    ('Chen',  '2024-03-01');

SELECT name, signup_date FROM users ORDER BY signup_date DESC;

W ten sposób najnowsze rejestracje są na początku. Daty zapisane jako teksty ISO 8601 (YYYY-MM-DD) sortują się poprawnie jako tekst. To jeden z powodów, dla których ten format jest zalecany dla kolumn z datami w SQLite, który nie ma osobnego typu daty.

Sortowanie po wielu kolumnach

Gdy w pierwszej kolumnie sortowania są remisy, SQLite rozstrzyga je drugą kolumną. Wypisz kolumny po przecinku, w kolejności ważności:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

INSERT INTO users (name, country) VALUES
    ('Boris', 'FR'),
    ('Ada',   'US'),
    ('Rosa',  'FR'),
    ('Chen',  'US');

SELECT name, country FROM users ORDER BY country, name;

Wiersze są najpierw grupowane po kraju (FR przed US), a w obrębie każdego kraju sortowane po imieniu. Każda kolumna może mieć własny kierunek:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT,
    signup_date TEXT
);

INSERT INTO users (name, country, signup_date) VALUES
    ('Boris', 'FR', '2024-03-12'),
    ('Ada',   'US', '2024-01-05'),
    ('Rosa',  'FR', '2024-02-20'),
    ('Chen',  'US', '2024-03-01');

SELECT name, country, signup_date
FROM users
ORDER BY country ASC, signup_date DESC;

Kraj rosnąco, a w każdym kraju od najnowszych. ASC i DESC dotyczą kolumny, przy której stoją, i nie przechodzą na kolejne.

Sortowanie po wyrażeniach i aliasach

ORDER BY przyjmuje dowolne wyrażenie, nie tylko nazwy kolumn. Przydaje się to przy wartościach obliczanych:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    quantity INTEGER
);

INSERT INTO products (name, price, quantity) VALUES
    ('Zeszyt', 4.50, 30),
    ('Długopis', 1.20, 100),
    ('Lampa',   25.00, 5);

SELECT name, price * quantity AS revenue
FROM products
ORDER BY revenue DESC;

Alias revenue z listy SELECT można śmiało użyć w ORDER BY. Możesz też zapisać wyrażenie jeszcze raz, ORDER BY price * quantity DESC, i zadziała tak samo.

Można też sortować po pozycji kolumny, choć lepiej unikać tego nawyku:

SELECT name, price FROM products ORDER BY 2 DESC;

2 oznacza drugą kolumnę na liście wyboru. To działa, ale jeśli ktoś później zmieni kolejność kolumn, sortowanie po cichu zmieni znaczenie. Sortuj raczej po nazwie lub aliasie.

Gdzie trafiają wartości NULL

NULL to "nieznane", a SQLite musi zdecydować, gdzie przy sortowaniu umieścić nieznane wartości. Domyślna zasada: NULL są na początku przy ASC i na końcu przy DESC.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   NULL),
    ('Rosa',  '2024-02-20'),
    ('Chen',  NULL);

SELECT name, signup_date FROM users ORDER BY signup_date;

Ada i Chen pojawiają się na górze, przed jakąkolwiek prawdziwą datą. Rzadko o to chodzi, gdy chcesz "najnowsze najpierw". Zmień to przez NULLS LAST:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   NULL),
    ('Rosa',  '2024-02-20'),
    ('Chen',  NULL);

SELECT name, signup_date FROM users
ORDER BY signup_date DESC NULLS LAST;

Teraz najpierw są prawdziwe daty, a NULL na dole. NULLS FIRST robi odwrotnie. Oba są częścią standardowego SQL i działają w SQLite 3.30 i nowszych.

Sortowanie bez rozróżniania wielkości liter z COLLATE NOCASE

Domyślne porównywanie tekstu w SQLite jest binarne: sortuje po punktach kodowych Unicode. Oznacza to, że wielkie litery trafiają przed małe, więc 'Zoe' jest przed 'apple':

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

INSERT INTO users (name) VALUES ('apple'), ('Zoe'), ('Boris'), ('ada');

SELECT name FROM users ORDER BY name;

Wynik to Boris, Zoe, ada, apple: najpierw wielkie litery, potem małe. Aby sortować bez rozróżniania wielkości liter, dodaj porównanie NOCASE:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

INSERT INTO users (name) VALUES ('apple'), ('Zoe'), ('Boris'), ('ada');

SELECT name FROM users ORDER BY name COLLATE NOCASE;

Teraz dostajesz ada, apple, Boris, Zoe. NOCASE traktuje jako równoważne tylko litery ASCII A-Z i a-z: nie normalizuje znaków diakrytycznych ani liter spoza ASCII (na przykład ą, ę czy ł). Do prawdziwego sortowania wielojęzycznego potrzebne jest porównanie po stronie aplikacji, ale w typowym angielskim przypadku NOCASE w zupełności wystarcza.

Losowa kolejność

Czasem potrzebujesz wierszy w losowej kolejności: gdy wybierasz wyróżnioną pozycję dnia albo losujesz wiersze do testów. Funkcja random() w SQLite zwraca losową liczbę całkowitą, więc sortuj po niej:

CREATE TABLE quotes (id INTEGER PRIMARY KEY, text TEXT);

INSERT INTO quotes (text) VALUES
    ('Najpierw niech działa, potem niech działa dobrze, potem szybko.'),
    ('Przedwczesna optymalizacja jest źródłem wszelkiego zła.'),
    ('Proste jest lepsze niż złożone.'),
    ('Czytelność się liczy.');

SELECT text FROM quotes ORDER BY random() LIMIT 1;

Każdy wiersz dostaje świeżą losową wartość, a sortowanie je tasuje. Na małych tabelach to wystarczy. Na dużych ORDER BY random() jest wolne: musi obliczyć losową wartość dla każdego wiersza i posortować cały wynik. Do wylosowania jednego wiersza z ogromnej tabeli szybsze są sprytniejsze sposoby (na przykład wybór losowego rowid).

Typowe pułapki

Kilka rzeczy, na których ludzie się potykają:

• Brak ORDER BY i zakładanie kolejności. Bez tej klauzuli kolejność jest nieokreślona. Nawet jeśli wygląda na stałą, taka nie jest.
• Sortowanie liczb zapisanych jako tekst. Leksykograficznie '10' jest przed '2'. Jeśli kolumna ma się sortować liczbowo, zapisz ją z liczbowym powinowactwem typu (albo rzutuj: ORDER BY CAST(value AS INTEGER)).
• Mieszanie ASC i DESC w różnych kolumnach. Każda kolumna ma własny kierunek. ORDER BY a, b DESC sortuje a rosnąco, a b malejąco, a nie obie malejąco.
• Sortowanie ogromnego wyniku tylko po to, by wziąć kilka pierwszych wierszy. Połącz ORDER BY z LIMIT i załóż indeks na kolumnie sortowania. O tym jest następna strona.

Dalej: LIMIT i OFFSET

Sortowanie mówi SQLite, jak ułożyć wiersze, a LIMIT i OFFSET mówią, ile ich zwrócić i od którego zacząć. Razem są podstawą stronicowania i zapytań typu "top N". O nich za chwilę.