Jak klauzulą ORDER BY sortować wynik zapytania SQL? – lekcja 13

Wstęp

Dane z bazy danych wysyłane są do klienta w przypadkowej kolejności. Aby zapewnić sobie porządek, którego potrzebujesz wykorzystujesz klauzulę ORDER BY. Z niniejszej lekcji dowiesz się o sposobach jej użycia.

Sortowanie wartości

Trzeba pamiętać, że dane w bazie danych przechowywane są bezładnie na stercie. Układanie ich w określonej kolejności wymaga kosztownego sortowania. Motor bazy danych zrobi to na Twoje wyraźne życzenie. Wyrażasz je umieszczając na końcu zapytania klauzulę ORDER BY. Trzeba ją umieścić przed klauzulą FETCH.

Po klauzuli ORDER BY wskazujesz listę kolumn według których sortowanie ma nastąpić. Kolumnę możesz wskazać na trzy sposoby. Pierwszy to wskazanie poprzez podanie nazwy kolumny.

SELECT
  owner
  ,table_name
FROM
  all_tables
ORDER BY
  owner
  ,table_name;

Drugi sposób to wskazanie numeru jej pozycji w klauzuli SELECT.

SELECT
  owner
  ,table_name
FROM
  all_tables
ORDER BY
  1
  ,2;

Jest to najmniej bezpieczne i najmniej czytelne z uwagi na możliwe późniejsze modyfikacje kolejności kolumn w klauzuli SELECT. Niemniej przydatne do szybkich zapytań ad-hoc.

Trzeci sposób to wskazanie kolumny poprzez jej alias. O aliasowaniu mówiłem we wcześniejszej lekcji.

SELECT
  owner "właściciel"
  ,table_name "nazwa tabeli"
FROM
  all_tables
ORDER BY
  "właścieciel"
  ,"nazwa tabeli";

Zachęcam do używania aliasów kolumn. Według mnie to najbardziej bezpieczny sposób. Nazwa kolumny i jej pozycja w klauzuli SELECT może się zmienić. Trzeba mieć na uwadze, że schemat tabeli ulega modyfikacjom.

Warto pamiętać, że w Oracle możesz sortować według kolumn, których nie ma w klauzuli SELECT. Ważne aby należały do tabel znajdujących się w klauzuli FROM, a w klauzuli SELECT nie był użyty DISTINCT.

SELECT
  owner
  ,table_name
FROM
  all_tables
ORDER BY
  tablespace_name;

Możesz wskazać instancji czy dane ma sortować rosnąco – ASC czy malejąco DESC.

Sposób sortowania wskazujesz dla każdej kolumny osobno.

SELECT
  owner
  ,table_name
FROM
  all_tables
ORDER BY
  owner ASC
  ,table_name DESC;

Tym sposobem wynik posortowany będzie według kolumny owner rosnąco, a według kolumny table_name malejąco.

Sortowanie NULL

W jednej z wcześniejszych lekcji mówiłem o NULL. Pamiętasz? Że jest stanem i wymaga specjalnego traktowania. Także w przypadku sortowania jest specjalnie traktowany. Mianowicie NULL nie jest sortowany. Jest doklejany do wyniku. Różne motory doklejają go w różnych miejscach. Oracle i PostgreSQL na końcu, a SQL Server i MySQL na początku.

Co możesz z tym zrobić gdy chcesz pisać przenaszalny kod?

Możesz użyć klauzul przygotowanych w standardzie ANSI języka SQL. Klauzula NULLS FIRST zagwarantuje, że niezależnie od motoru i porządku sortowania NULL będzie na początku wyniku.

SELECT
  tablespace_name
FROM
  all_tables
ORDER BY
  tablespace_name
NULLS FIRST;

Używając NULLS LAST wymuszasz umieszczenie NULL na końcu.

SELECT
  tablespace_name
FROM
  all_tables
ORDER BY
  tablespace_name
NULLS LAST;

Gdy w klauzuli SELECT użyjesz DISTINCT to w ORDER BY możesz użyć tylko kolumn objętych DISTINCT. Nie wierz mi na słowo. Sprawdź jak jest u Ciebie i daj znać.

Polskie sortowanie

W przykładach używałem danych składających się ze znaków ASCII. Warto mieć na uwadze, że sortowanie polskich znaków diakrytycznych zależy od parametru COLLATE użytego w definicji kolumny i tabeli. Zależy także od parametrów z jakimi została utworzona baza danych, wystartowana instancja czy jakie ustawiasz dla swojej sesji w bazie danych.

Temat jest na tyle obszerny, że zasługuje na osobny materiał. W komentarzach możesz namówić mnie do jego realizacji.

Tymczasem zapamiętaj, że operator COLLATE użyte w klauzuli ORDER BY wymusi określony sposób sortowania. Na przykład sortowanie według polskich zasad.

SELECT
  table_name
FROM
  all_tables
ORDER BY
  table_name COLLATE polish;

Takie sortowanie uwzględni wielkość liter. Czyli

A, a, A, ą, B, b, C, c, Ć, ć, D, d, E, e, Ę, ę, F, f, G, g, H, h, I, i, J, j, K, k, L, l, Ł, ł, M, m, N, n, Ń, ń, O, o, Ó, ó, P, p, R, r, S, s, Ś, ś, T, t, U, u, W, w, Y, y, Z, z, Ź, ź, Ż, ż.

Gdy chcesz zignorować wielkość liter użyj polish z przyrostkiem _ci. Czyli case insensitive.

SELECT
  table_name
FROM
  all_tables
ORDER BY
  table_name COLLATE polish_ci;

Możliwe są inne narodowe sortowania. Przydatne gdy Twoja baza danych przechowuje napisy w różnych językach, a Ty chcesz je prezentować wg narodowego sortu.

Jeśli chcesz pogłębić temat samodzielnie to zerknij do dokumentacji.

Podsumowanie

Gratuluję Ci ukończenia trzynastej lekcji darmowej edycji mojego kursu 'Podstawy SQL dla użytkowników Oracle’. Wiesz z niej jak przy pomocy klauzuli ORDER BY sortować wartości i NULLe. Wiesz, że w klauzuli ORDER BY kolumny możesz wskazać wykorzystując nazwę kolumny, jej pozycję w klauzuli SELECT lub alias. Według mnie najbezpieczniej po aliasie.

Używając sortowania w bazie danych trzeba mieć w pamięci, że jest kosztowne. Zużywa tym więcej zasobów im więcej danych jest do posortowania i im częściej Twoje zapytanie będzie używane. Pisanie optymalnych zapytań sortujących wymaga wiedzy o indeksach. O tym w innych materiałach.

Zadanie dla Ciebie: napisz zapytanie zwracające wartości kolumn owner, table_name i num_rows z all_tables. Wybierz wiersze w których num_rows jest większe niż dziesięć. Wynik posortuj według kolumn owner rosnąco, table_name rosnąco i num_rows malejąco.

Jeśli potrzebujesz podpowiedzi wbijaj na discord. Tam też chwal się swoim rozwiązaniem prezentując zrzut ekranu swojego klienta. Dedykowany serwer: Marcin Badtke – bazy danych i SQL.

Wiesz już jak używać klauzuli ORDER BY do sortowania wartości i NULLi. W następnej lekcji pokażę Ci jak wybrać z wyniku jedynie wiersze zawierające topowe wartości.

Tymczasem dziękuję za ukończenie niniejszej lekcji.

Wiesz co trzeba zrobić aby nie przegapić kolejnych.

Epilog

Pewną wariacją sortowania jest losowe układanie wierszy wyniku. Jak zapewne pamiętasz motor bazy danych nie gwarantuje kolejności zwracanych wierszy. Niemniej ta losowość jest determinowana przez ścieżkę dostępu do danych wybraną przez optymalizator. Aby zagwarantować sobie różną kolejność za każdym wykonaniem zapytania sortuj wg losowych liczb.

SELECT
  table_name
FROM
  all_tables
ORDER BY
  DBMS_RANDOM.RANDOM;

DBMS_RANDOM jest standardowym pakietem dostarczanym razem z bazą danych przez Oracle. Zawiera różnego rodzaju podprogramy wspierające obsługę wartości losowych. W tym przypadku używam funkcji RANDOM zwracającej losową liczbę całkowitą. Użyta w taki sposób zwróci inną liczbę dla każdego rekordu wyniku.

Wróć do poprzedniej lekcji