Природно, нумероваться рядки повинні відповідно до деякого порядком. Нехай цей порядок задається стовпцем первинного ключа, тобто в порядку зростання (або зменшення) значень в цьому єдиному стовпці. Для визначеності припустимо, що нам потрібно перенумерувати моделі в таблиці Product, де номер моделі якраз є первинним ключем. Істотним тут є те, значення первинного ключа не містять дублікатів і NULL-значення, в результаті чого є принципова можливість встановити однозначну відповідність між номером моделі і номером рядка в заданому порядку сортування моделей.
Розглянемо спочатку наступний запит:
SELECT P1.model, P2.model
FROM Product P1 JOIN
Product P2 ON P1.model <= P2.model
Тут виконується з'єднання двох ідентичних таблиць за нерівністю P1.model <= P2.model, в результате чего каждая модель из второй таблицы (P2.model) будет соединяться только с теми моделями из первой таблицы (P1.model), номера которых меньше или равны номеру этой модели. В результате получим, например, что модель с минимальным номером (1121) будет присутствовать во втором столбце результирующего набора только один раз, так как она меньше или равна только самой себе. На другом конце будет находиться модель с максимальным номером, так как любая модель будет меньше или равна ей. Следовательно, модель с максимальным номером будет сочетаться с каждой моделью, и число таких сочетаний будет равно общему числу моделей в таблице Product.
Зі сказаного вище ясно, що це кількість разів, яке кожна з моделей зустрічається в другому стовпці результуючого набору якраз і буде порядковим номером моделі при сортуванні моделей по зростанню.
Таким чином, щоб вирішити нашу задачу нумерації досить перерахувати моделі в правій колонці, що неважко зробити за допомогою угруповання і використання агрегатної функції COUNT:
SELECT COUNT (*) no, P2.model
FROM Product P1 JOIN
Product P2 ON P1.model <= P2.model
GROUP BY P2.model
Для нумерації в зворотному порядку досить поміняти знак нерівності на протилежний.
Якщо ваша СУБД підтримує ранжирующие функції. то пронумерувати рядки можна зовсім просто:
SELECT ROW_NUMBER () OVER (ORDER BY model) no, model
функція row_number
Функція ROW_NUMBER, як випливає з її назви, нумерує рядки, які повертаються запитом. З її допомогою можна виконати більш складне упорядкування рядків у звіті, ніж те, яке дає пропозицію ORDER BY в рамках Стандарту SQL-92.
До появи цієї функції для нумерації рядків, що повертаються запитом, доводилося використовувати досить складний інтуїтивно незрозумілий алгоритм. Єдиною перевагою даного алгоритму є те, що він буде працювати практично на всіх СУБД, що підтримують стандарт SQL-92.
Використовуючи функцію ROW_NUMBER можна:
задати нумерацію, яка буде відрізнятися від порядку сортування рядків результуючого набору;
створити "некрізну" нумерацію, тобто виділити групи із загального безлічі рядків і пронумерувати їх окремо для кожної групи;
використовувати одновмеренно кілька способів нумерації, оскільки, фактично, нумерація не залежить від сортування рядків запиту.
Найпростіше можливості функції ROW_NUMBER показати на простих прикладах, до чого ми і переходимо.
Приклад 1: Пронумерувати всі рейси з таблиці Trip в порядку зростання їх номерів. Виконати сортування по.
SELECT row_number () over (ORDER BY trip_no) num,
WHERE ID_comp <3
ORDER BY id_comp, trip_no
Пропозиція OVER, з яким використовується функція ROW_NUMBER задає порядок нумерації рядків. При цьому використовується додаткова пропозиція ORDER BY, яке не має відношення до порядку виведення рядків запиту
А якщо потрібно пронумерувати рейси для кожної компанії окремо? Для цього нам буде потрібно ще одна конструкція в реченні OVER - PARTITION BY.
Конструкція PARTITION BY задає групи рядків, для яких виконується незалежна нумерація. Група визначається рівністю значень в списку стовпців, перерахованих в цій конструкції, у рядків, що складають групу.
Приклад 2: Пронумерувати рейси кожної компанії окремо в порядку зростання номерів рейсів.
SELECT row_number () over (partition BY id_comp ORDER BY id_comp, trip_no) num,
WHERE ID_comp <3
ORDER BY id_comp, trip_no
PARTITION BY id_comp означає, що рейси кожної компанії утворюють групу, для якої і виконується незалежна нумерація.
Відсутність конструкції PARTITION BY, як це био в першому прикладі, означає, що всі рядки результуючого набору утворюють одну єдину групу.
Для продовження скачування необхідно зібрати картинку: