SELECT II

V této kapitole se dozvíte o dalších kouzlech, co umí příkaz SELECT. Dosud jste se naučili selektovat jen řádky a sloupce, tak jak jsou uložené v databázi. Teď se naučíte, jak zpracovávat souhrné (statistické) informace. Level vašich znalostí o SQL zase stoupne o jednu laťku nahoru.

WHERE

Podmínku WHERE jste se naučili používat již v kaptiole Úprava tabulky. Tuto podmínku, na rozdíl od následujících, využívají i příkazy jako DELETE FROM a UPDATE. Odkaz na tuto podmínku je tady jen pro úplnost.

V této kapitole využiji tabulky z příkladu kapitoly Vytváření relací. Dozvíte se tu spoustu zajímavých a potřebných věcí, které byste měli velmi dobře znát.

ORDER BY

ORDER BY způsobí setřídění řádků podle nějakého sloupce (abecedně nebo podle velikosti čísel). ORDER BY se píše až za podmínku WHERE (pokud ji použijete).

Na začátek ještě vložím jeden řádek do databáze, který se bude v této lekci později hodit.

INSERT INTO zamestnanci VALUES ('Pan', 'Novy', NULL, 1, 8001010612);

A teď už příklad k ORDER BY.

rimmer1=> SELECT * FROM zamestnanci ORDER BY prijmeni;
  jmeno  | prijmeni | plat  | oddeleni_id | rodne_cislo
---------+----------+-------+-------------+-------------
 Tom     | Jerry    | 15000 |           2 | 8001010605
 Leopold | King     | 13000 |           2 | 8001010607
 Martin  | Luter    | 12000 |           2 | 8001010606
...
(11 rows)

rimmer1=> SELECT * FROM zamestnanci WHERE plat >=15000 ORDER BY prijmeni;
 jmeno | prijmeni | plat  | oddeleni_id | rodne_cislo
-------+----------+-------+-------------+-------------
 Tom   | Jerry    | 15000 |           2 | 8001010605
 Tomas | Mann     | 22000 |           3 | 8001010608
 Lenka | Pavova   | 15000 |           2 | 8001010604
 Vasek | Trn      | 16000 |           3 | 8001010609
(4 rows)

Setřídění probíhá implicitně vzestupně – ASC, nebo můžete třídit sestupně – DESC.

rimmer1=> SELECT * FROM zamestnanci WHERE plat >=15000 ORDER BY plat DESC;
 jmeno | prijmeni | plat  | oddeleni_id | rodne_cislo
-------+----------+-------+-------------+-------------
 Tomas | Mann     | 22000 |           3 | 8001010608
 Vasek | Trn      | 16000 |           3 | 8001010609
 Lenka | Pavova   | 15000 |           2 | 8001010604
 Tom   | Jerry    | 15000 |           2 | 8001010605
(4 rows)

Třídit lze podle více sloupců než podle jednoho. V následujícím SELECTu setřídím výsledek nejdříve podle platu (setstupně) a tam kde je plat stejný setřídím záznam podle jména (vzestupně).
Priorita třídění je dána pořadím sloupců za klíčovými slovy ORDER BY.

rimmer1=> SELECT * FROM zamestnanci WHERE plat >=15000
ORDER BY plat DESC, prijmeni;
 jmeno | prijmeni | plat  | oddeleni_id | rodne_cislo
-------+----------+-------+-------------+-------------
 Tomas | Mann     | 22000 |           3 | 8001010608
 Vasek | Trn      | 16000 |           3 | 8001010609
 Tom   | Jerry    | 15000 |           2 | 8001010605
 Lenka | Pavova   | 15000 |           2 | 8001010604
(4 rows)

V klauzuli ORDER BY můžete použít nejen jméno sloupce, ale v podstatě jakýkoliv výraz, který se může použít i za SELECTem. Můžete zkrátka třídit podle jakéhokoliv sloupce, který se objeví ve výsledku. A co víc, můžete dokonce třídit i podle sloupců (nebo výrazů), které se neobjeví ve výsledku!

rimmer1=> SELECT * FROM zamestnanci WHERE plat >=15000
ORDER BY plat % 5000, plat DESC, prijmeni;
 jmeno | prijmeni | plat  | oddeleni_id | rodne_cislo
-------+----------+-------+-------------+-------------
 Tom   | Jerry    | 15000 |           2 | 8001010605
 Lenka | Pavova   | 15000 |           2 | 8001010604
 Vasek | Trn      | 16000 |           3 | 8001010609
 Tomas | Mann     | 22000 |           3 | 8001010608
(4 řádky)

NULLS FIRST

Podívejte se na následující příklad:

rimmer1=> SELECT * FROM zamestnanci WHERE oddeleni_id = 1 ORDER BY plat;
 jmeno | prijmeni | plat  | oddeleni_id | rodne_cislo
-------+----------+-------+-------------+-------------
 Lenka | Pavova   | 10000 |           1 | 8001010601
 Jana  | Pavova   | 10000 |           1 | 8001010602
 Jana  | Mala     | 12000 |           1 | 8001010603
 Pan   | Novy     |       |           1 | 8001010612
(4 řádky)

Dá se nějak rozumě vysvětlit, proč je Pan Novy poslední, když jeho plat je NULL? Jestli vás napadlo, že je NULL menší než všechny čísla, tak jste nedávali pozor. NULL znamená, že nevím jaký má Pan Novy plat. Důvot je ten, že Postgres prostě implicitně hodnoty s NULL řadí na konec.

To se dá změnit pomocí klauzule NULLS FIRST:

rimmer1=> SELECT * FROM zamestnanci WHERE oddeleni_id = 1
ORDER BY plat NULLS FIRST, prijmeni, jmeno;
 jmeno | prijmeni | plat  | oddeleni_id | rodne_cislo
-------+----------+-------+-------------+-------------
 Pan   | Novy     |       |           1 | 8001010612
 Jana  | Pavova   | 10000 |           1 | 8001010602
 Lenka | Pavova   | 10000 |           1 | 8001010601
 Jana  | Mala     | 12000 |           1 | 8001010603
(4 řádky)

Pozor! Pokud použijete NULLS FIRST, je jedno jestli řadíte sloupec sestupně (DESC) nebo vzestupně (ASC), hodnoty s NULL budou vždy první.

Za domácí úkol zjistětě z dokumentace, jak se dá explicitně říct, že mají být hodnoty NULL na konci. (Buďte raději paranoidní a implicitnímu nastavení nevěřte. Nikdy nevíte, kdy se může změnit!)

Pokud nepoužijete ORDER BY, řádky se vracejí v náhodném pořadí. Možná by se vám z výsledků mohlo někdy zdát, že se vracejí v pořadí v jakém jste je vložili do tabulky, ale to je jen „náhoda“. Bez ORDER BY se opravdu nemůžete na pořadí spolehnout.

COUNT, AVG, MIN, MAX, SUM, STDDEV

Příkaz SELECT může získat některé „statistické“ údaje pomocí následujících agregačních funkcí: Průměr AVG, minimum MIN, maximum MAX, součet SUM, počet položek COUNT, variace VARIACE a směrodatná odchylka STDDEV. Celý výčet agregačních funkcí najdete v dokumentaci PostgreSQL.

rimmer1=> SELECT COUNT(plat) FROM zamestnanci;
 count
-------
    10
(1 řádka)

Příkaz vrátil počet řádků, kde je uveden plat, tj. kde plat není NULL. Pokud vás zajímá počet řádků (tj. bez ohledu na to, zda je nebo není v nějakém sloupci NULL), použijte funkci COUNT s hvězdičkou *.

Zkusím přidat zaměstnance, kde neuvedu jeho plat. Nezapomeňte, že 0 je také číslo, takže jej COUNT započítává. Proto je třeba uvést NULL. Počet „platů“ v tabulce se tím nezmění (přestože počet řádků ano).

rimmer1=> SELECT COUNT(plat) AS platů, COUNT(*) AS řádek FROM zamestnanci;
 platů | řádek
-------+-------
    10 |    11
(1 řádka)

Další příklad ukazuje funkci MAX:

rimmer1=> SELECT MAX(plat) FROM zamestnanci;
  max
-------
 22000
(1 row)

Všimli jste si něčeho zajímavého? Když jsem popisoval speciální hodnotu NULL, psal jsem, že NULL znamená, že nevím, co tam je. Jak teda můžu vědět, když mám v sloupečku plat jednu hodnotu NULL, jaká je maximální hodnota? Neměl by být výsledek taky NULL (že nevím, jaký je výsledek)?
Věc se má tak, že MAX prostě NULL ignoruje. Jediný případ, kdy by MAX vrátilo NULL by bylo, kdyby bylo NULL ve všech řádcích.
Obdobně to funguje i s ostatními agregačními funkcemi.

Další příklad už asi nepotřebuje komentář:

rimmer1=> SELECT
MAX(plat) AS "maximalni plat",
MIN(plat) AS "minimalni plat",
COUNT(plat) AS "pocet platu",
COUNT(*) AS radku,
AVG(plat) AS prumerny_plat
FROM zamestnanci;
 maximalni plat | minimalni plat | pocet platu | radku |   prumerny_plat    
----------------+----------------+-------------+-------+--------------------
          22000 |           9000 |          10 |    11 | 13400.000000000000
(1 řádka)

rimmer1=> SELECT
MAX(plat) AS "maximalni plat",
MIN(plat) AS "minimalni plat",
COUNT(plat) AS "pocet platu",
COUNT(*) AS radku,
AVG(plat) AS "prumerny plat"
FROM zamestnanci
WHERE oddeleni_id = 1;
 maximalni plat | minimalni plat | pocet platu | radku |     prumerny plat      
----------------+----------------+-------------+-------+------------------------
          12000 |          10000 |           3 |     4 | 10666.6666666666666667
(1 řádka)

rimmer1=> SELECT STDDEV(plat) FROM zamestnanci;
     stddev
-----------------
 3835.5066303047
(1 row)

GROUP BY

Předchozí (agregační) funkce pracují s celou tabulkou a vrátí jeden souhrný řádek. Pomocí podmínky WHERE můžete některé řádky ze zpracování vyřadit, ale výsledek pak bude stále jeden souhrný řádek pro všechny zbylé řádky.

Představte si, že chcete zjistit výsledky pro každé oddělení zvlášť. Jednou z možností je pro každé oddělení použít podmínku WHERE oddeleni_id = číslo oddělení. Tak ale budete muset spustit pro každé oddělení extra SELECT. Samozřejmě to jde jednodušeji (tak si nestěžujte že se musíte učit zase něco nového, ušetří vám to spoustu práce :-).

K tomu, abyste zjistili souhrnná data (maximum, minimum atd.) pro skupiny řádků, slouží klauzule GROUP BY, která řádky seskupí (vytvoří skupiny z řádků obsahujících stejnou hodnotu v seskupovaném sloupci).

V příkladu zobrazím minima, maxima, počet vyplněných platů a počet záznamů oddělení.

rimmer1=> SELECT MAX(plat), MIN(plat), COUNT(plat), COUNT(*) AS "řádek"
FROM zamestnanci GROUP BY oddeleni_id;
 max  |  min  | count | řádek
-------+-------+-------+-------
 12000 | 10000 |     3 |     4
 22000 |  9000 |     3 |     3
 15000 | 12000 |     4 |     4
(3 řádky)

Z předchozího příkladu nepoznáte, kterému oddělení patří který řádek. Naštěstí sloupce podle kterých se seskupuje (jsou uvedeny v GROUP BY) můžete přidat do výpisu.

Pokud použijete GROUP BY, smíte vypsat pouze sloupce podle kterých se seskupuje (jsou za GROUP BY), nebo agregované sloupce.
Výsledný řádek je totiž vždy výsledkem zpracování několika řádků. Kdybyste chtěli vypsat sloupec bez použití agregační funkce, nebylo by jasné, z jakého řádku se má použít hodnota do výsledku. Tento problém se sloupci z GROUP BY odpadá – každý řádek výsledku je seskupením řádků pro stejnou hodnotou groupovacího sloupečku.

rimmer1=> SELECT oddeleni_id, MAX(plat), MIN(plat), COUNT(plat), STDDEV(plat)
FROM zamestnanci GROUP BY oddeleni_id ORDER BY oddeleni_id;
 oddeleni_id |  max  |  min  | count |      stddev      
-------------+-------+-------+-------+-------------------
           1 | 12000 | 10000 |     3 | 1154.700538379252
           2 | 15000 | 12000 |     4 | 1500.000000000000
           3 | 22000 |  9000 |     3 | 6506.407098647712
(3 řádky)

Ještě pár příkladů:

rimmer1=> SELECT oddeleni_id, MAX(plat), MIN(plat), COUNT(plat)
FROM zamestnanci WHERE oddeleni_id !=3 GROUP BY oddeleni_id
ORDER BY MAX(plat) DESC;
 oddeleni_id |  max  |  min  | count
-------------+-------+-------+-------
           2 | 15000 | 12000 |     4
           1 | 12000 | 10000 |     3
(3 rows)

rimmer1=> SELECT max(plat), min(plat), plat FROM zamestnanci;
ERROR:  column "zamestnanci.plat" must appear in the GROUP BY clause or be used in an aggregate function
ŘÁDKA 1: SELECT max(plat), min(plat), plat FROM zamestnanci;
                                      ^
I agregační funkce lze použít v klauzuli ORDER BY.

Pokud přeci jen chcete vypsat sloupec, který není v GROUP BY, protože víte že i tak je ve všech řádcích použitých pro výsledný řádek hodnota stejná, použijte třeba funkci MAX. Když víte, že jsou hodnoty stejné, tak MAX nemůže vrátit nic jiného, než tu hodnotu (stejně tak MIN).

rimmer1=> SELECT oddeleni_id,
MAX(o.nazev) AS nazev,
max(plat), min(plat)
FROM oddeleni o, zamestnanci z
WHERE o.prim_klic = z.oddeleni_id
GROUP BY oddeleni_id
ORDER BY oddeleni_id;
 oddeleni_id |      nazev      |  max  |  min  
-------------+-----------------+-------+-------
           1 | Sekretariat     | 12000 | 10000
           2 | Pravni oddeleni | 15000 | 12000
           3 | Pravni oddeleni | 22000 |  9000
(3 řádky)

V klauzuli GROUP BY můžete uvést více sloupců. Řekněme třeba, že mě zajímá jak často se která výše platu objevuje v závislosti na oddělení:

rimmer1=> SELECT plat,oddeleni_id,count(plat)
FROM zamestnanci
GROUP BY oddeleni_id, plat
ORDER BY plat DESC, oddeleni_id;
 plat  | oddeleni_id | count
-------+-------------+-------
       |           1 |     0
 22000 |           3 |     1
 16000 |           3 |     1
 15000 |           2 |     2
 13000 |           2 |     1
 12000 |           1 |     1
 12000 |           2 |     1
 10000 |           1 |     2
  9000 |           3 |     1
(9 řádek)

Z výsledku se třeba dočtu, že plat 12000 se vyskytuje jednou v oddělení id 1 a jednou v oddělení id 2 a že plat 10000 se objevuje jen dvakrát v oddělení id 1. Na prvním řádku je plat NULL. Objevuje se v oddělení s id 1, ale COUNT vrací 0, protože hodnotu NULL funkce COUNT(plat) nezapočítá. (Zkuste místo toho COUNT(*).)

Poznámka: Pořadí klauzulí WHEREGROUP BYORDER BY nelze zaměňovat.

HAVING

Podmínka HAVING je ekvivalentní podmínce WHERE jen s tím rozdílem, že podmínka WHERE se aplikuje před provedením příkazu GROUP BY (odstraní řádky před zgroupováním) a HAVING se aplikuje až na řádky vytvořené po zgroupování (tedy na řádky výsledku SELECT … GROUP BY).

V prvním příkladu vyberu z tabulky zamestnanci jen ty řádky, které neobsahují oddělení č. 1 a z nich získám souhrnné informace pro ostatní oddělení. V druhém příkladu získám souhrnné informace ze všech oddělení, ale výslednou tabulku omezím na oddělení, které nemají oddeleni_id č. 1.
Dvěma různými cestami tak získám stejný výsledek.

rimmer1=> SELECT oddeleni_id, MAX(plat), MIN(plat), COUNT(plat)
FROM zamestnanci WHERE oddeleni_id <> 1 GROUP BY oddeleni_id;
 oddeleni_id |  max  |  min  | count
-------------+-------+-------+-------
           2 | 15000 | 12000 |     4
           3 | 22000 |  9000 |     3
(2 rows)

rimmer1=> SELECT oddeleni_id, MAX(plat), MIN(plat), COUNT(plat)
FROM zamestnanci GROUP BY oddeleni_id HAVING oddeleni_id != 1;
 oddeleni_id |  max  |  min  | count
-------------+-------+-------+-------
           2 | 15000 | 12000 |     4
           3 | 22000 |  9000 |     3
(2 rows)

Mezi operátorem <> a != není rozdíl, oboje znamená „nerovná se“ a oboje můžete libovolně zaměňovat.

Zajímavější bude následující příklad, kde zobrazím jen ta oddělení, kde je maximální plat větší jak 15000. Vzhledem k tomu, že se omezení týká výsledku agregační funkce, lze toho dosáhnout pouze pomocí HAVING (WHERE se aplikuje před zjištěním tohoto výsledku).

rimmer1=> SELECT oddeleni_id, MAX(plat),MIN(plat), COUNT(plat)
FROM zamestnanci GROUP BY oddeleni_id HAVING MAX(plat) > 15000;
 oddeleni_id |  max  | min  | count
-------------+-------+------+-------
           3 | 22000 | 9000 |     3
(1 row)

Všiměte si, že zatímco WHERE se píše před GROUP BY, HAVING se píše až za. To není náhoda :-).
PS: Nic vám nebrání používat WHERE i HAVING v jednom SQL dotazu :-).

LIMIT a OFFSET

LIMIT a OFFSET jsou poslední dvě věci, které vám chyběli, abyste mohli pracovat s tabulkami o milionech řádků.
LIMIT omezí počet vrácených řádků a OFFSET přeskočí zadaný počet prvních řádek.

Jejich použití je tak jednoduché a jasné, že bych snad ani nemusel dělat příklad. Ale pro jistotu:

rimmer=> SELECT * FROM zamestnanci ORDER BY prijmeni LIMIT 3;
  jmeno  | prijmeni | plat  | oddeleni_id | rodne_cislo
---------+----------+-------+-------------+-------------
 Tom     | Jerry    | 15000 |           2 | 8001010605
 Leopold | King     | 13000 |           2 | 8001010607
 Martin  | Luter    | 12000 |           2 | 8001010606
(3 rows)

rimmer=> SELECT * FROM zamestnanci ORDER BY prijmeni LIMIT 3 OFFSET 1;
  jmeno  | prijmeni | plat  | oddeleni_id | rodne_cislo
---------+----------+-------+-------------+-------------
 Leopold | King     | 13000 |           2 | 8001010607
 Martin  | Luter    | 12000 |           2 | 8001010606
 Jana    | Mala     | 12000 |           1 | 8001010603
(3 řádky)

LIMIT i OFFSET musí být kladná celá čísla.
Poznámka: příkaz OFFSET lze použít i bez klauzule LIMIT.

MySQL/MariaDB

Výčet agregačních funkcí MySQL najdete v dokumentaci. Asi vás nepřekvapí, že se od PostgreSQL lyší.

MySQL má trochu jinou strategii pro pojmenování sloupců kde použijete agregační funkce než PostgreSQL. Raději vždy používejte vlastní jména pomocí AS.

MySQL také umožní přidat do SELECTu sloupce, které nejsou součástí GROUP BY. Hodnota v takovém sloupci se vybere náhodně z řádků, které jsou seskupené (v příkladě je hodnota třetího sloupce náhodně vybrána ze všech řádků tabulky). Že je to fuj a že byste měli používat trik s MAX (nebo MIN) asi nemusím dodávat.

SELECT max(plat), min(plat), plat FROM zamestnanci;
+-----------+-----------+-------+
| max(plat) | min(plat) | plat  |
+-----------+-----------+-------+
|     22000 |      9000 | 10000 |
+-----------+-----------+-------+
1 row in set (0.00 sec)

MySQL nemá pro ORDER BY klauzuli NULL FIRST, ale může si pomoci jiným trikem: Nejdříve se seřadí sloupec plat podle toho, jestli je nebo není NULL (použije se funkce ISNULL) a pak už podle samotného sloupce plat.

mysql> SELECT * FROM zamestnanci WHERE oddeleni_id = 1
ORDER BY plat IS NULL DESC, plat, prijmeni, jmeno;
+-------+----------+-------+-------------+-------------+
| jmeno | prijmeni | plat  | oddeleni_id | rodne_cislo |
+-------+----------+-------+-------------+-------------+
| Pan   | Novy     |  NULL |           1 | 8001010612  |
| Jana  | Pavova   | 10000 |           1 | 8001010602  |
| Lenka | Pavova   | 10000 |           1 | 8001010601  |
| Jana  | Mala     | 12000 |           1 | 8001010603  |
+-------+----------+-------+-------------+-------------+
4 rows in set (0.00 sec)

Tento trik funguje i v PostgreSQL a SQLite, ale ne v Oracle (tam alespoň funguje NULLS IF).

Příjdete na to, co z SQL dotazu odstranit, aby byli hodnoty s NULL na konci?

MySQL umí LIMIT a OFFSET jako Postgres (jenom vyžaduje toto pořadí), ale umí ještě něco navíc, co je (bohužel) hojně používáno.
Pokud zapíšete za LIMIT dvě čísla (oddělená čárkou), první se bere jako offset a druhé jako limit.

Typický MySQLák píše takto:

mysql> SELECT * FROM zamestnanci ORDER BY prijmeni LIMIT 1,3;
+---------+----------+-------+-------------+-------------+
| jmeno   | prijmeni | plat  | oddeleni_id | rodne_cislo |
+---------+----------+-------+-------------+-------------+
| Leopold | King     | 13000 |           2 | 8001010607  |
| Martin  | Luter    | 12000 |           2 | 8001010606  |
| Jana    | Mala     | 12000 |           1 | 8001010603  |
+---------+----------+-------+-------------+-------------+
3 rows in set (0.00 sec)

Je to kratší, ale nestandardní.

SQLite

Agregační funkce SQLite také najdete v nápovědě. Zjistíte, že SQLite nemá STDDEV (standardní odchylku) a není moc, co byste stím mohli udělat (leda si tuto funkci doprogramovat – to nežertuji).

SQLite umožňuje přidat sloupeček do SELECTu, který není součástí GROUP BY stejně jako MySQL.

SQLite neumí NULLS FIRST. Můžete ale použít stejný trik, jako u MySQL.

SQLite umí, stejně jako MySQL, mimo standardního LIMIT a OFFSET (pouze v tomto pořadí, pokud použijete obé) i nestandardní offset s limitem pomocí LIMIT offset,limit.

Oracle

Agregační funkce najdete, jako vždy, v dokumentaci.

Oracle neumožňuje SELECTovat sloupeček, který není součástí GROUP BY. Trik s MAX samozřejmě funguje.

Oracle umí NULLS FIRST. Pokud byste ale chtěli použít trik s ORDER BY plat IS NULL, tak to neumí, protože neumí použít plat IS NULL jako součást klauzule SELECT (na rozdíl od ostatních databází).

Můžete použít jiný trik, který bude fungovat všude. Používá ale CASE, který budu vysvětlovat až v příští kapitole. Takže teď ten trik ukáži prozatím bez vysvětlení:

oracle> SELECT * FROM zamestnanci WHERE oddeleni_id = 1
ORDER BY CASE WHEN plat IS NULL THEN 0 ELSE 1 END,
plat, prijmeni, jmeno;
JMENO                PRIJMENI                   PLAT ODDELENI_ID RODNE_CISL
-------------------- -------------------- ---------- ----------- ----------
Pan                  Novy                                      1 8001010612
Jana                 Pavova                    10000           1 8001010602
Lenka                Pavova                    10000           1 8001010601
Jana                 Mala                      12000           1 8001010603

LIMIT a OFFSET

Oracle nemá LIMIT ani OFFSET. To je bezesporu šokující zjištění. Abyste mohli omezit výstupní řádky, budete muset použít trik, který využívá dvě novinky: pseudosloupeček ROWNUM a něco co už umíte, jen o tom nevíte – SELECT ze SELECTu.

Tak nejdřív co je to ten SELECT ze SELECTu. V povídání o CREATE VIEW jste se dozvěděli, že výsledkem každého SELECTu je tabulka. Pomocí CREATE VIEW jste si tuto „tabulku“ pojmenovali a mohli jste z ní SELECTovat. Ono to jde ale i přímo ze SELECTu!

SELECT * FROM  (
SELECT * FROM zamestnanci ORDER BY prijmeni
) z;

Toto funguje ve všech DBMS. Rozdíl je jen v pojmenování výsledku vnitřního SELECTu. MySQL a PostgreSQL vyžadují, aby byl pojmenovaný. Ostatní databáze to umožňují, ale nevyžadují. Oracle to také umožňuje (viz příklad), ale nemůžete použít AS nazev_tabulky (slovo AS tam nesmí být). Všechny ostatní DBMS AS povolují (ale nevyžadují).

ROWNUM je pseudosloupeček, který obsahuje číslo řádku výsledné tabulky SELECTu. ROWNUM umí ze zde zmiňovaných databází jen Oracle.

ROWNUM je právě to, co se dá použít pro omezení počtu řádků.
Následuje příklad LIMITu:

oracle> SELECT * FROM  (
SELECT * FROM zamestnanci ORDER BY prijmeni
)
WHERE ROWNUM <= 3;
JMENO                PRIJMENI                   PLAT ODDELENI_ID RODNE_CISL
-------------------- -------------------- ---------- ----------- ----------
Tom                  Jerry                     15000           2 8001010605
Leopold              King                      13000           2 8001010607
Martin               Luter                     12000           2 8001010606

Pokud by vás napadlo, že by to šlo jednodušeji a vystačili byste si s jedním příkazem, tak nevystačili. Problém je v tom, že při použití ORDER BY se řádky SELECTují v zatím neseřazeném pořadí, už se jim přiděluje ROWNUM a porovnává se ve WHERE podmínce.

oracle> SELECT * FROM zamestnanci WHERE ROWNUM <= 3 ORDER BY prijmeni;
JMENO                PRIJMENI                   PLAT ODDELENI_ID RODNE_CISL
-------------------- -------------------- ---------- ----------- ----------
Jana                 Mala                      12000           1 8001010603
Lenka                Pavova                    10000           1 8001010601
Jana                 Pavova                    10000           1 8001010602

Výsledekem je, že sice vyberete 3 řádky seřazené podle příjmení, ale ne první 3 řádky. Vyberete náhodné 3 řádky, které projdou podmínkou WHERE. Až na nich se provede seřazení pomocí ORDER BY.

Jak simulovat OFFSET? Možná vás napadlo toto:

SELECT * FROM (
SELECT * FROM zamestnanci ORDER BY prijmeni
) z  WHERE ROWNUM BETWEEN 2 AND 4;

To ale nevrátí žádný řádek. Proč? ROWNUM se přiděluje řádkům, které jsou součástí výsledku dotazu. První řádek má číslo 1, druhý řádek č. 2 atd. Jenže, když první řádek neprojde podmínkou WHERE, stává se z dalšího řádku v řadě řádek první. Takže ten taky neprojde podmínkou WHERE atd. atd.
SELECT s podmínkou WHERE ROWNUM > 1 nikdy nevrátí žádný řádek.

Kvůli OFFSETu se musí provést ještě jeden vnořený SELECT navíc.

oracle> SELECT * FROM (
    SELECT z.*, ROWNUM rnum FROM (
        SELECT * FROM zamestnanci z ORDER BY prijmeni
    ) z WHERE ROWNUM <= 4
)
WHERE rnum >= 2;

JMENO   PRIJMENI    PLAT    ODDELENI_ID RODNE_CISLO RNUM
Leopold King        13000   2           8001010607  2
Martin  Luter       12000   2           8001010606  3
Jana    Mala        12000   1           8001010603  4

Funguje to asi takhle: Dva vnořené SELECTy fungují jako LIMIT. Prostřední SELECT navíc udělá z ROWNUM (toho nejvíce vnořeného SELECTu) regulérní non-pseudo sloupec rnum. V posledním, vnějším SELECTu už se s tímto sloupcem může pracovat jako s jakýmkoliv jiným reálným sloupcem.

Je to hnus, ale je to Oraclem doporučovaný oficiální postup pro LIMIT a OFFSET. Dobrá zpráva aspoň je, že ten úplně vnitřní SELECT nevybere úplně všechny data z disku do paměti, kde by se teprve vybrali požadované řádky. Díky optimalizaci dotazů dokáže Oracle takovéto SELECTy provádět opravdu rychle (jako jakákoliv jiná databáze s LIMITem a OFFSETem).

V Oracle verze 12c už můžete použít top N queries klauzuli.

Komentář Hlášení chyby
Vytvořeno: 11.9.2005
Naposledy upraveno: 9.9.2015
Tato stánka používá ke svému běhu cookies, díky kterým je možné monitorovat, co tu provádíte (ne že bych to bez cookies nezvládl). Také vás tu bude špehovat google analytics. Jestli si myslíte, že je to problém, vypněte si cookies ve vašem prohlížeči, nebo odejděte a už se nevracejte :-). Prohlížením tohoto webu souhlasíte s používáním cookies. Dozvědět se více..