SQL‑запрос для получения второго по величине количества

Условие

В базе присутствуют две связанные таблицы — works и materials. Требуется сформировать запрос, который вернёт значение quantity, являющееся вторым по величине в таблице materials.

CREATE TABLE works
(
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE materials
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    quantity INTEGER,
    work_id BIGINT,
    FOREIGN KEY (work_id) REFERENCES works(id) ON DELETE CASCADE
);

INSERT INTO works(id, name)
VALUES (1, 'work 1'),
       (2, 'work 2'),
       (3, 'work 3');

INSERT INTO materials(id, name, quantity, work_id)
VALUES (1, 'mat 1', 10, 1),
       (2, 'mat 2', 20, 2),
       (3, 'mat 2', 15, 2),
       (4, 'mat 4', 30, 2);

Ответ

Для второго различного значения quantity можно взять максимум среди значений меньше общего максимума. На приведённых данных результат равен 20; соединение с works не требуется.

SELECT MAX(quantity) AS second_quantity
FROM materials
WHERE quantity < (SELECT MAX(quantity) FROM materials);

Повторения максимального значения не влияют на ответ. Если различных ненулевых значений меньше двух, результат NULL. NULL не участвует в MAX.

Если имеется в виду именно вторая строка сортировки, включая одинаковые количества, нужен другой контракт: ORDER BY quantity DESC NULLS LAST с OFFSET 1 и LIMIT 1, а для устойчивого выбора строки — дополнительная сортировка по id.