Пошук уроків, статей та іншого контенту
Відсортуєте результати й обмежите вибірку, створюючи сторінкову навігацію за допомогою LIMIT та OFFSET.
ORDER BYБез явного сортування PostgreSQL не гарантує порядок рядків у результаті запиту. Навіть якщо під час кількох запусків рядки виглядають однаково впорядкованими, покладатися на це не варто.
Для сортування використовується ORDER BY:
SELECT column1, column2
FROM table_name
ORDER BY column1;ASCНаприклад:
SELECT name, price
FROM products
ORDER BY price;Цей запит поверне товари від найдешевшого до найдорожчого.
Для явного зазначення напрямку використовують:
ASC — за зростанням;
DESC — за спаданням.
-- Від найдешевшого товару до найдорожчого
SELECT name, price
FROM products
ORDER BY price ASC;
-- Від найдорожчого товару до найдешевшого
SELECT name, price
FROM products
ORDER BY price DESC;ASC можна не вказувати, оскільки це значення за замовчуванням.
В ORDER BY можна вказати кілька стовпців через кому:
SELECT name, category, price
FROM products
ORDER BY category ASC, price DESC;Спочатку результати будуть згруповані за категорією в алфавітному порядку. Усередині кожної категорії товари будуть відсортовані за ціною від більшої до меншої.
Порядок стовпців має значення:
ORDER BY category, price;Спочатку PostgreSQL порівнює category. Значення price використовуються лише для рядків, у яких категорія однакова.
Наведений приклад можна виконати в PostgreSQL повністю:
CREATE TEMP TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
category text NOT NULL,
price numeric(10, 2) NOT NULL
);
INSERT INTO products (id, name, category, price)
VALUES
(1, 'Механічна клавіатура', 'Аксесуари', 2500.00),
(2, 'Миша', 'Аксесуари', 1200.00),
(3, 'Монітор 24"', 'Монітори', 7000.00),
(4, 'Монітор 27"', 'Монітори', 10500.00),
(5, 'USB-кабель', 'Аксесуари', 300.00),
(6, 'Ноутбук', 'Компʼютери', 32000.00);
-- Найдорожчі товари на початку
SELECT id, name, price
FROM products
ORDER BY price DESC;
-- Сортування за категорією, а всередині категорії — за ціною
SELECT id, name, category, price
FROM products
ORDER BY category ASC, price DESC;LIMITLIMIT обмежує кількість рядків, які повертає запит:
SELECT id, name, price
FROM products
ORDER BY price DESC
LIMIT 3;Запит поверне лише три найдорожчі товари.
LIMIT зазвичай використовують разом із ORDER BY. Без сортування ви не можете надійно визначити, які саме рядки потраплять у вибірку.
Наприклад:
SELECT id, name, price
FROM products
ORDER BY price DESC
LIMIT 1;Це запит для отримання найдорожчого товару.
LIMIT 0LIMIT 0 не повертає рядків, але запит залишається коректним:
SELECT id, name
FROM products
LIMIT 0;Це може бути корисно, коли потрібно перевірити структуру результату запиту без отримання даних.
OFFSETOFFSET вказує, скільки рядків потрібно пропустити перед поверненням результату:
SELECT id, name, price
FROM products
ORDER BY price DESC
OFFSET 2;У цьому прикладі PostgreSQL пропустить перші два рядки після сортування і поверне всі наступні.
На практиці OFFSET часто використовують разом із LIMIT:
SELECT id, name, price
FROM products
ORDER BY price DESC
LIMIT 2
OFFSET 2;Порядок виконання тут такий:
Результати сортуються за ціною від більшої до меншої.
Пропускаються перші два рядки.
Повертаються наступні два рядки.
Сторінкова навігація, або pagination, розбиває великий список на сторінки.
Наприклад, якщо на одній сторінці має бути 3 товари:
перша сторінка: LIMIT 3 OFFSET 0;
друга сторінка: LIMIT 3 OFFSET 3;
третя сторінка: LIMIT 3 OFFSET 6.
Загальна формула:
OFFSET = (номер_сторінки - 1) * кількість_елементів_на_сторінціПриклад для сторінки товарів:
-- Сторінка 1: рядки з 1 до 3
SELECT id, name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 3
OFFSET 0;
-- Сторінка 2: рядки з 4 до 6
SELECT id, name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 3
OFFSET 3;Стовпець id додано другим у сортування, щоб зробити порядок стабільним, якщо кілька товарів мають однакову ціну.
У застосунку номер сторінки та розмір сторінки зазвичай надходять від користувача або клієнтського інтерфейсу.
Наприклад:
page = 3
pageSize = 10Тоді значення OFFSET буде:
(3 - 1) * 10 = 20SQL-запит матиме такий вигляд:
SELECT id, name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 10
OFFSET 20;Для коректної сторінкової навігації порядок рядків має бути передбачуваним. Якщо сортувати лише за стовпцем, значення якого може повторюватися, PostgreSQL не зобов’язаний визначати порядок рядків із однаковими значеннями.
Наприклад:
SELECT id, name, price
FROM products
ORDER BY price DESC
LIMIT 3
OFFSET 3;Якщо кілька товарів мають однакову ціну, їхній взаємний порядок може змінюватися.
Краще додати унікальний стовпець як додатковий критерій:
SELECT id, name, price
FROM products
ORDER BY price DESC, id ASC
LIMIT 3
OFFSET 3;Тепер:
товари сортуються за ціною від більшої до меншої;
товари з однаковою ціною сортуються за id від меншого до більшого.
Це робить результати сторінок стабільнішими.
FETCHPostgreSQL також підтримує стандартний SQL-синтаксис FETCH FIRST:
SELECT id, name, price
FROM products
ORDER BY price DESC
FETCH FIRST 3 ROWS ONLY;Для PostgreSQL у повсякденному коді частіше використовують коротший запис із LIMIT:
SELECT id, name, price
FROM products
ORDER BY price DESC
LIMIT 3;Для сторінкової навігації LIMIT та OFFSET є простим і зрозумілим вибором.
LIMIT без ORDER BYSELECT *
FROM products
LIMIT 3;Такий запит повертає три рядки, але не гарантує, які саме. Для отримання конкретних перших рядків потрібно додати сортування:
SELECT *
FROM products
ORDER BY id ASC
LIMIT 3;LIMIT та OFFSETПравильний синтаксис:
SELECT *
FROM products
ORDER BY id
LIMIT 10
OFFSET 20;Не потрібно розміщувати OFFSET перед LIMIT.
ORDER BY під час пагінаціїЗапит із LIMIT та OFFSET без стабільного сортування може повертати непередбачувані сторінки:
SELECT *
FROM products
LIMIT 10
OFFSET 10;Для сторінкової навігації використовуйте ORDER BY, бажано з унікальним додатковим стовпцем:
SELECT *
FROM products
ORDER BY price DESC, id ASC
LIMIT 10
OFFSET 10;OFFSETЯкщо розмір сторінки дорівнює 10, то для третьої сторінки потрібно пропустити 20 рядків, а не 3:
SELECT *
FROM products
ORDER BY id
LIMIT 10
OFFSET 20;OFFSETЩо більше значення OFFSET, то більше рядків PostgreSQL може бути змушений опрацювати перед поверненням результату. Тому дуже глибокі сторінки можуть працювати повільніше.
На початковому етапі достатньо пам’ятати: LIMIT визначає розмір сторінки, а OFFSET — кількість пропущених рядків.
ORDER BY сортує результати запиту.
ASC сортує за зростанням і є значенням за замовчуванням.
DESC сортує за спаданням.
У ORDER BY можна вказати кілька стовпців.
LIMIT обмежує кількість повернених рядків.
OFFSET пропускає вказану кількість рядків.
Для сторінкової навігації використовують комбінацію ORDER BY, LIMIT та OFFSET.
Формула для OFFSET: (номер сторінки - 1) * розмір сторінки.
Для стабільного порядку сторінок додавайте унікальний стовпець, наприклад id, до ORDER BY.