Zaczniemy od wyjaśnienia tytułu dzisiejszego wpisu. "krawat" to po angielsku "tie", słówko z wieloma znaczeniami.
Efekt: głupi tytuł wpisu. Ale tylko tytuł, bowiem dalej robi się ciekawie.
W pierwszej części autor tłumaczy tytuł wpisu.
W drugiej zaś...
Do dzieła!
Wyobraźmy sobie, że mamy zdenormalizowaną tabelę wyników w jakiejś bliżej nieokreślonej dziedzinie sportowej. Chcemy sprawdzić, kto wygrał, a także, jak wygląda kolejność poszczególnych zawodników:
IF OBJECT_ID('tempdb..#wyniki') IS NOT NULL
DROP TABLE #wyniki;
WITH cte1 AS (
SELECT 'Jan Kowalski' osoba, 18 wynik
UNION ALL SELECT 'Jan Malinowski', 23
UNION ALL SELECT 'Barbara Klumpf', 16
UNION ALL SELECT 'Hubert Grzyb', 19
UNION ALL SELECT 'Aniela Chytry', 37
UNION ALL SELECT 'Kalina Szyszka', 92
UNION ALL SELECT 'Grzegorz Grzelichowski', 37
UNION ALL SELECT 'Alojzy Babel', 76
)
SELECT * INTO #wyniki FROM cte1;
SELECT * FROM #wyniki
ORDER BY wynik DESC;
Wynik powyższego zapytania wygląda tak:
osoba wynik
Kalina Szyszka 92
Alojzy Babel 76
Aniela Chytry 37
Grzegorz Grzelichowski 37
Jan Malinowski 23
Hubert Grzyb 19
Jan Kowalski 18
Barbara Klumpf 16
Na razie wszystko jasne. Mamy listę zawodników, posortowaną malejąco według wyników. Wygrała pani Kalina, za nią na drugim miejscu jest pan Alojzy, a trzecie miejsce ex aequo należy do pani Anieli i pana Grzegorza.
Załóżmy teraz, że chcemy wyłonić trzech najlepszych zawodników, żeby ich postawić na podium.
Pierwsze i drugie miejsca - sprawa jest oczywista. Ale kogo postawić na miejscu trzecim?
Jeżeli damy tam pana Grzegorza, wrzask podniosą feministki. Jeżeli panią Anielę, zostaniemy posądzeni o rasizm (pan Grzegorz, choć tak nie brzmi, jest czarny jak sfera Schwarzschilda). Wypadałoby zbudować specjalne podium czteroosobowe... Tylko jak zmusić SQL-a, żeby w takiej sytuacji nikogo nie faworyzował? No bo tak:
IF OBJECT_ID('tempdb..#wyniki') IS NOT NULL
DROP TABLE #wyniki;
WITH cte1 AS (
SELECT 'Jan Kowalski' osoba, 18 wynik
UNION ALL SELECT 'Jan Malinowski', 23
UNION ALL SELECT 'Barbara Klumpf', 16
UNION ALL SELECT 'Hubert Grzyb', 19
UNION ALL SELECT 'Kalina Szyszka', 92
UNION ALL SELECT 'Grzegorz Grzelichowski', 37
UNION ALL SELECT 'Aniela Chytry', 37
UNION ALL SELECT 'Alojzy Babel', 76
)
SELECT * INTO #wyniki FROM cte1;
SELECT TOP 3 * FROM #wyniki
ORDER BY wynik DESC;
W wyniku dostajemy:
osoba wynik
Kalina Szyszka 92
Alojzy Babel 76
Grzegorz Grzelichowski 37
Ale już:
IF OBJECT_ID('tempdb..#wyniki') IS NOT NULL
DROP TABLE #wyniki;
WITH cte1 AS (
SELECT 'Jan Kowalski' osoba, 18 wynik
UNION ALL SELECT 'Jan Malinowski', 23
UNION ALL SELECT 'Barbara Klumpf', 16
UNION ALL SELECT 'Hubert Grzyb', 19
UNION ALL SELECT 'Kalina Szyszka', 92
UNION ALL SELECT 'Aniela Chytry', 37
UNION ALL SELECT 'Grzegorz Grzelichowski', 37
UNION ALL SELECT 'Alojzy Babel', 76
)
SELECT * INTO #wyniki FROM cte1;
SELECT TOP 3 * FROM #wyniki
ORDER BY wynik DESC;
Daje na wyjściu:
osoba wynik
Kalina Szyszka 92
Alojzy Babel 76
Aniela Chytry 37
Jak widać, SQL Server działa tu na zasadzie "kto pierwszy, ten lepszy" i na trzecim miejscu umieszcza osobę, która jako pierwsza była wstawiona do tabeli wyników.
Co więc robić, panie premierze?
Odpowiedź jest prosta: KRAWATY!
Trzeba wybrać osoby z krawatami!
O, tak:
IF OBJECT_ID('tempdb..#wyniki') IS NOT NULL
DROP TABLE #wyniki;
WITH cte1 AS (
SELECT 'Jan Kowalski' osoba, 18 wynik
UNION ALL SELECT 'Jan Malinowski', 23
UNION ALL SELECT 'Barbara Klumpf', 16
UNION ALL SELECT 'Hubert Grzyb', 19
UNION ALL SELECT 'Kalina Szyszka', 92
UNION ALL SELECT 'Aniela Chytry', 37
UNION ALL SELECT 'Grzegorz Grzelichowski', 37
UNION ALL SELECT 'Alojzy Babel', 76
)
SELECT * INTO #wyniki FROM cte1;
SELECT TOP 3 WITH TIES * FROM #wyniki
ORDER BY wynik DESC;
Dodanie klauzuli "WITH TIES" da nam na wyjściu cztery rekordy:
osoba wynik
Kalina Szyszka 92
Alojzy Babel 76
Aniela Chytry 37
Grzegorz Grzelichowski 37
Używam SQL-a od tak dawna, że czasem mi się myli i zamiast normalnie poprosić Żonę o widelec z szafki, mówię do niej SELECT widelec FROM szafka. Przerażające jest, że ona to rozumie, ale to temat na kiedy indziej 😉 W każdym razie chcę powiedzieć, że sądziłem, iż wszystkie zakamarki komendy SELECT są mi już dobrze znane. Tymczasem proszę - WITH TIES.
Dziwny świat.
Ha!
po kiego grzyba używasz #tabel skoro masz cte?
dodatkowo, zapytanie zadziałało poprawnie [to bez krawata] – chciałeś 3 pierwsze rekordy. Do celów o których piszesz powinno się używać RANK
Użyłem #tabeli, bo początkowo miałem trochę bardziej ambitne plany, które jednakowoż zdechły (w końcu to tylko pchełka jest).
Co do użycia RANK zamiast WITH TIES – różnica jest zasadnicza. RANK jest bardziej skomplikowane (zarówno składniowo, jak i obliczeniowo), no i RANK działa inaczej niż WITH TIES. RANK wymagałoby podania dodatkowego warunku WHERE. W przypadku RANK, gdyby na drugim miejscu były dwie osoby ex aequo, cała logika by się rozjechała. I tak dalej. TOP N WITH TIES nie wymaga żadnego agregowania ani żadnego filtrowania danych – jedyne, co robi, to zapewnia, że wynik będzie „uczciwy”, w tym sensie, że nie odrzuci żadnego rekordu „remisującego” z N-tym, kosztem potencjalnego zwiększenia liczby rekordów wynikowych (a więc trzeba po prostu pamiętać, że używając WITH TIES rekordów na wyjściu MOŻE być więcej niż N).