Data Science. SQL hub

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



Решить задачу без использования подзапроса можно. Правда, для этого используются нестандартные средства. Метод основывается на конструкции 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