K čemu je dobrá funkce Rank() a PARTITION BY u SQL Serveru

16.07.2020, Vladimír Klaus, navštíveno 2264x

Při propojování tabulek typu "master-slave" občas potřebujete k hlavnímu záznamu přidat jen jeden z podřízených záznamů, a to na základě nějaké podmínky nebo spíše pořadí. Tedy například u zákazníka chcete vidět poslední objednávku nebo třeba u auta jeho první opravu apod.

Níže uvádím příklady z mé praxe. U prvního jde o to, jak z každého záznamu o baterii vybrat jen nejnovější záznam. A protože se záznamy přidávají automaticky, stačí se orientovat jen podle ID.

Zde tedy seskupím podle IdBaterie, uvnitř srovnám podle ID sestupně, tedy od nejnovějšího záznamu a tomuto pořadí přiřadím "rank" počínaje 1. No a pak se už jen celý výsledek vyfiltruje tak, aby se použily jen ty "1".

SELECT * FROM (
	SELECT *, Rank() OVER (PARTITION BY IdBaterie ORDER BY ID DESC) RankOrder
	FROM Baterie
) a
WHERE RankOrder = 1

Druhý příklad je vlastně totožný, jen ukazuje, že se například dá vybrat i nejstarší záznam dle data, zde ještě v kombinaci s další podmínkou. Proč je zde důležité datum nemusí být úplně zřejmé – nikde není zaručeno, že později přidaný záznam (vyšší ID) bude mít také novější datum.

SELECT * FROM (
	SELECT *, Rank() OVER (PARTITION BY IdKocky ORDER BY Datum) RankOrder
	FROM Ockovani WHERE JeOckovani='False'
) a
WHERE RankOrder = 1

Samozřejmě jsou zde i další možnosti (především pro databáze, které nedisponují výše uvedenými možnostmi). V tomto případě potřebuji k produktu připojit nějaký soubor z fotogalerie tohoto produktu. Přidáním další podmínky můžete blíže definovat, který soubor to bude. Další detaily najdete v tomto článku.

SELECT Nazev,
    (
        SELECT TOP 1 Soubor FROM Fotogalerie f
        WHERE f.IdProduktu = p.ID
    )
FROM Produkty p