ROWNUM – bardzo fajnie, ale…

Dzial developerow

PORADY I SZTUCZKI

W zapytaniach SQL chcemy często zawrzeć wyświetlanie liczby
porządkowej, czyli numerować po kolei wszystkie wyświetlane rekordy. Jest to
szczególnie potrzebne w prostych raportach generowanych za pomocą skryptów SQL-wych. Na
pierwszy rzut oka można to zrobić w trywialny sposób, używając do tego pseudokolumny
ROWNUM. Problemy zaczynają się jednak gdy chcemy wynik zapytania posortować.

Rozważmy dla przykładu zapytanie wyświetlające listę pracowników:

select rownum lp, nazwisko, imie
from pracownicy

Wynik, jaki otrzymamy spełnia dokładnie to o co nam chodzi:

lp nazwisko imie
— ————  ——————-
1 ZAKRZEWICZ MACIEJ
2 CHMIELEWSKI RAFAŁ
3 MATYSIAK MACIEJ
4 GÓRECKI WOJCIECH

Jednak gdy zechcemy wyświetlić pracowników w kolejności
alfabetycznej:

select rownum lp, nazwisko, imie
from pracownicy
order by nazwisko

to otrzymany wynik już nie spełnia naszych oczekiwań:

lp nazwisko imie
— ————  ——————-
2 CHMIELEWSKI RAFAŁ
4 GÓRECKI WOJCIECH
3 MATYSIAK MACIEJ
1 ZAKRZEWICZ MACIEJ

Trudno nawet znaleźć zastosowanie dla wartości ROWNUM w takim
przypadku, gdyż nie reprezentują one dla użytkownika żadnej wartości; pokazują
jedynie pewną wiedzę na temat składowania rekordów w bazie danych.
Problem ten można rozwiązać konstruując nieco inaczej zapytanie SQL. Oznacza to, że
rezygnujemy z użycia ROWNUM i sami wyliczymy liczbę porządkową każdego rekordu.
Rozważmy następujące zapytanie:

select count(*) lp, p1.nazwisko, p1.imie
from pracownicy p1, pracownicy p2
where p1.nazwisko >= p2.nazwisko
group by p1.nazwisko
order by p1.nazwisko

Wynik jaki otrzymamy będzie dokładnie taki jak chcemy:

lp nazwisko imie
— ————  ——————-
1 CHMIELEWSKI RAFAŁ
2 GÓRECKI WOJCIECH
3 MATYSIAK MACIEJ
4 ZAKRZEWICZ MACIEJ

Idea działania zapytania jest następująca: każda grupa rekordów
utworzona w wyniku grupowania zawiera rekord z danym pracownikiem i wszystkich
pracowników, którzy w alfabecie pojawiają się wcześniej niż ten pracownik, a funkcja
count(*) podaje tę liczbę. Pierwszy pracownik na liście jest jedynym rekordem w takiej
grupie gdyż nie ma poprzedników. Grupa ostatniego pracownika na liście zawiera jego
samego i wszystkich poprzednich.
Powyższe zapytanie działa poprawnie w przypadku gdy nazwiska są unikalne i
jednoznacznie identyfikują rekordy. Jeśli tak nie jest (a najczęściej nie jest) to
warunek połączeniowy należy rozbudować w taki sposób, aby zawierał wszystkie
atrybuty wchodzące w skład klucza unikalnego. Załóżmy, że nazwisko i imię będą
jednoznacznie identyfikować pracowników. Wówczas zapytanie przybiera postać:

select count(*) lp, p1.nazwisko, p1.imie
from pracownicy p1, pracownicy p2
where p1.nazwisko||p1.imie >= p2.nazwisko||p2.imie
group by p1.nazwisko, p1.imie
order by p1.nazwisko, p1.imie

Jeśli klucz unikalny jest jeszcze bardziej złożony to w analogiczny
sposób należy rozbudować warunek połączeniowy. Jak widać w klauzuli GROUP BY i ORDER
muszą być podane wszystkie atrybuty tworzące klucz unikalny.

Maciej Matysiak
maciej.matysiak@cs.put.poznan.pl
Instytut Informatyki Politechniki Poznańskiej