Kuidas kasutada õigesti TOP (MS Access) ja FETCH FIRST n ROWS WITH TIES (PostgreSQL) predikaate väärtuste jadas kohal x olevate väärtuste leidmiseks?

• TOP n - saab kasutada MS Accessis (samaväärset TOP ... WITH TIES saab kasutada MS SQL Serveris). SQL standard ei kirjelda.
• LIMIT n - Saab kasutada nt PostgreSQLis (ja ka nt LibreOffice Baseis). SQL standard ei kirjelda.
• FETCH FIRST n ROWS ONLY - Samaväärne LIMITiga. Saab kasutada nt PostgreSQLis. Seda kirjeldab SQL standard.
• FETCH FIRST n ROWS WITH TIES - Samaväärne MS Accessi TOPiga. Saab kasutada nt PostgreSQLis. Seda kirjeldab SQL standard.

Kõik need predikaadid põhinevad sellel, et read sorteeritakse ja valitakse esimesed read.

• LIMIT n ja FETCH FIRST n ROWS ONLY tagastavad maksimaalselt n rida.
• TOP n ja FETCH FIRST n ROWS WITH TIES võivad tagastada rohkem kui n rida. Kui sorteerimise aluseks olevas veerus on reas n mingi väärtus, mis on ka järgnevates ridades, siis on tulemuses ka need read.

NB! PostgreSQLis saaks järgneva kahe ülesande lahendamiseks edukalt kasutada ka Dense_rank aknafunktsiooni. See leiab tiheda pingerea. Tihedas pingereas on asetused näiteks 1,1,2, mitte 1,1,3. Lahenduseks on leida suuruste või esinemiste arvu pingerida ja valida sealt kolmandal kohal olevad tulemused.

Loon näitena tabeli

CREATE TABLE Vaartus (suurus SMALLINT NOT NULL);

INSERT INTO Vaartus(suurus) VALUES (10);
INSERT INTO Vaartus(suurus) VALUES (10);
INSERT INTO Vaartus(suurus) VALUES (10);
INSERT INTO Vaartus(suurus) VALUES (9);
INSERT INTO Vaartus(suurus) VALUES (9);
INSERT INTO Vaartus(suurus) VALUES (9);
INSERT INTO Vaartus(suurus) VALUES (8);
INSERT INTO Vaartus(suurus) VALUES (8);
INSERT INTO Vaartus(suurus) VALUES (7);

Ülesanne 1: Leia tabelist Vaartus read, kus on suuruselt kolmas suurus. Antud andmete korral tuleks leida read, kus suurus on 8.

Vale lahendus MS Accessis:

SELECT suurus
FROM Vaartus
WHERE suurus = (SELECT Min(suurus) AS m
FROM (SELECT TOP 3 suurus
FROM Vaartus
ORDER BY suurus DESC) AS ap);

Vale lahendus PostgreSQLis:

SELECT suurus
FROM Vaartus
WHERE suurus = (SELECT Min(suurus) AS m
FROM (SELECT suurus
FROM Vaartus
ORDER BY suurus DESC
FETCH FIRST 3 ROWS WITH TIES) AS ap);

Tulemuseks on read, kus suurus on 10.

Alampäring, kus on TOP/FETCH, ei leia mitte kolme erinevat suurimat suurust, vaid suuruse järgi kahanevalt sorteeritud tulemusest kolm esimest rida. Juhul kui on veel mõni rida, kus on esimeses kolmes reas olnud suurus, siis on ka see tulemuses. Antud juhul on tulemuses read, kus suurus on 10.

Õige lahendus MS Accessis:

SELECT suurus
FROM Vaartus
WHERE suurus = (SELECT Min(suurus) AS m
FROM (SELECT DISTINCT TOP 3 suurus
FROM Vaartus
ORDER BY suurus DESC) AS ap);

Õige lahendus PostgreSQLis:

SELECT suurus
FROM Vaartus
WHERE suurus = (SELECT Min(suurus) AS m
FROM (SELECT DISTINCT suurus
FROM Vaartus
ORDER BY suurus DESC
FETCH FIRST 3 ROWS WITH TIES) AS ap);

Sisemine alampäring leiab kolm erinevat kõige suuremat suurust. Välimine alampäring leiab nende hulgast kõige väiksema. Põhipäring leiab kõik read, kus on leitud suurus. Antud juhul võib alampäringus kasutada ka FETCH FIRST 3 ROWS ONLY või LIMIT, sest see ei muuda tulemust.

Ülesanne 2: Leia tabelist Vaartus esinemiste arvult kolmandal kohal olevad suurused. Väljasta suurus ja selle esinemiste arv. Antud andmete korral tuleks leida suurus 7, mida esineb üks kord.

Vale lahendus MS Accessis:

SELECT suurus, Count(*) AS arv
FROM Vaartus
WHERE suurus=(SELECT Min(suurus) AS m
FROM (SELECT TOP 3 suurus, Count(*) AS arv
FROM Vaartus
GROUP BY suurus
ORDER BY Count(*) DESC) AS ap)
GROUP BY suurus;

Vale lahendus PostgreSQLis:

SELECT suurus, Count(*) AS arv
FROM Vaartus
WHERE suurus=(SELECT Min(suurus) AS m
FROM (SELECT suurus, Count(*) AS arv
FROM Vaartus
GROUP BY suurus
ORDER BY Count(*) DESC
FETCH FIRST 3 ROWS WITH TIES) AS ap)
GROUP BY suurus;

Tulemuseks on suurus 8, mida esineb kaks korda.

Alampäring, kus on TOP/FETCH, ei leia mitte kolme erinevat suurimat suuruste esinemiste arvu, vaid arvu järgi kahanevalt sorteeritud tulemusest kolm esimest rida. Juhul kui on veel mõni rida, kus on esimeses kolmes reas olnud arv, siis on ka see tulemuses. Antud juhul on tulemuses kaks rida, kus arv on 3 ja üks rida, kus arv on kaks.

Õige lahendus MS Accessis:

SELECT suurus, Count(*) AS arv
FROM Vaartus
GROUP BY suurus
HAVING Count(*)=(SELECT Min(arv) AS m
FROM (SELECT DISTINCT TOP 3 Count(*) AS arv
FROM Vaartus
GROUP BY suurus
ORDER BY Count(*) DESC) AS ap);

Õige lahendus PostgreSQLis:

SELECT suurus, Count(*) AS arv
FROM Vaartus
GROUP BY suurus
HAVING Count(*)=(SELECT Min(arv) AS m
FROM (SELECT DISTINCT Count(*) AS arv
FROM Vaartus
GROUP BY suurus
ORDER BY Count(*) DESC
FETCH FIRST 3 ROWS WITH TIES) AS ap);

Sisemine alampäring leiab kolm erinevat kõige suuremat esinemiste arvu. Välimine alampäring leiab nende hulgast kõige väiksema. Põhipäring leiab erinevate suuruste esinemiste arvu ja jätab alles read, kus see arv on võrdne alampäringutega leitud arvuga. Antud juhul võib alampäringus kasutada ka FETCH FIRST 3 ROWS ONLY või LIMIT, sest see ei muuda tulemust.

Hinda postitust:
1 2 3 4 5

Keskmine hinne : Pole veel hinnanguid!