🖥 Подсказки и решения к задаче

Решить задачу без использования подзапроса можно. Правда, для этого используются нестандартные средства. Метод основывается на конструкции TOP N (SQL Server), которая позволяет отобрать из отсортированного набора первые N строк. Аналогичные конструкции имеются в диалектах SQL многих СУБД. В комитете по разработке стандартов было даже зафиксировано предложение о включение подобной конструкции в стандарт языка. Так что не исключено, что к моменту выхода этой книги в свет данная конструкция уже будет стандартизована. А вот и решение:

SELECT TOP 1 WITH TIES model, price
FROM Printer
ORDER BY price DESC;

Итак, выполняется сортировка по убыванию цены. В результирующий набор попадает одна (первая — TOP 1) строка. Однако остается проблема, когда несколько принтеров из таблицы будут иметь одинаковую максимальную цену. Проблема решается при помощи предложения WITH TIES, которое включит в результирующий набор не только N строк (в нашем случае одну), но и все ниже идущие строки, у которых значения в полях сортировки (у нас — price) совпадают со значениями N-ой строки (у нас — первой).

В PostgreSQL/MySQL для ограничения количества строк, возвращаемых запросом, используется конструкция (следующая после ORDER BY)

LIMIT N [OFFSET M]

где
N - число первых строк, возвращаемых запросом в порядке, заданном сортировкой;
M - число строк, пропускаемых перед началом вывода.
Если предложение OFFSET отсутствует, то выводиться будут N строк, начиная с первой, в противном случае - N строк, начиная с M+1.

Конструкций, подобных WITH TIES, в этих СУБД нет. Поэтому для решения рассматриваемой задачи способом через сортировку так или иначе придется использовать подзапрос:


SELECT model, price
FROM Printer WHERE price =
(SELECT price FROM Printer ORDER BY price DESC LIMIT 1);

Заметим, что в отличие от MySQL, в PostgreSQL предложение OFFSET может использоваться и при отсутствии предложения LIMIT. В этом случае возвращаться будут все строки запроса кроме первых M. Так, например, чтобы вывести все строки кроме первой строки с максимальной ценой, можно написать:

SELECT model, price
FROM Printer
ORDER BY price DESC OFFSET 1;

model price
1401 150.00
1408 270.00
1433 270.00
1434 290.00

При использовании SQL Server эту же задачу можно решить так:

SELECT model, price
FROM Printer WHERE code NOT IN(
SELECT TOP 1 code
FROM Printer
ORDER BY price DESC);
Т.е. мы выбираем все строки, кроме той, которая идёт первой при сортировке цены по убыванию.
А теперь попробуйте решить эту задачу под MySQL. 🙂

При решении последней задачи следует обратить внимание на неточность ее постановки. В результате чего представленное решение на одних и тех же данных может давать разные результаты при разных запусках. В частности, у нас имеется две строки с максимальной ценой, и какая из них будет выводиться зависит от порядка, в котором СУБД будет извлекать строки. А этот порядок может меняться в зависимости от выбранного оптимизатором плана. Чтобы сделать постановку и результат! однозначными, следует указать в условии задачи однозначный порядок сортировки. Эту однозначность всегда обеспечит включение первичного ключа в конец списка столбцов сортировки, например:

ORDER BY price DESC, code
или

ORDER BY price DESC, model, code

@sqlhub