Как удалить строки, полученные подзапросом с фильтрацией и сортировкой?

Question

[ieromi]

Дана таблица cities=(date, city, temperature)
Нужно удалить из таблицы два последних (по дате) измерения температуры для каждого города одним запросом.
Написал подзапрос select, который выводит нужные строки, но не знаю, как его отредактировать так, чтоб он их удалял.
Сам подзапрос:

SELECT date, city, temperature
FROM (SELECT *, row_number() OVER (PARTITION BY city ORDER BY date DESC) as r FROM cities
)
WHERE r = 1 or r = 2

Когда вставляю его в запрос DELETE - вылетает ошибка

SQL Error [42601]: ОШИБКА: в подзапросе слишком много столбцов

".
В итоге рабочий запрос выглядит так:

DELETE FROM cities WHERE ctid in (
	SELECT ctid FROM (
              SELECT ctid, date, city, temperature, row_number() OVER (PARTITION BY city ORDER BY date DESC) as r 
              FROM cities) as s
	WHERE s.r = 1 or s.r = 2
)

Comments

[Дмитрий]

Был бы в вашей таблице ещё и уникальный ид, то выбрали бы их иды и подставили в фильтр для удаления

[ieromi]

Данный вопрос я задавал в рамках выполнения тестового задания, где в условии про уникальные id для каждой записи не было сказано, поэтому и пришлось искать обходные пути.

Answer 1

[MVV]

Как известно, в однотабличном запросе - ровно одна таблица в FROM, без JOIN, подзапросы (в скобках) не в счет - удалить строки, которые выбирает оператор SELECT не просто, а очень просто: достаточно заменить SELECT (со списком полей) на DELETE.
Ваш запрос, начиная со второй строчки, можно преобразовать к такому виду: в FROM указать имя самой таблицы, а отбор строк сделать через подзапрос в разделе WHERE. Если в таблице есть первичный ключ из одного поля, то это делается проще всего через подзапрос для ANY, выбирающий нужный первичный ключ , а в общем случае это делается через подзапрос для EXISTS. Существующее условие WHERE надо перенести в подзапрос. А потом - просто заменить первую строчку на DELETE.

Comments

[ieromi]

Благодарю за ответ! Не смог пока разобраться с использованием EXISTS в подзапросе, а primary key из одного поля в таблице не предусмотрен, но я набрёл на аналогичный вопрос на stackoverflow. Там использовался ctid как primary key, и я использовал тот же прием.

[vreitech]

ieromi, аккуратнее. ctid нетранзакционен.

[ieromi]

vreitech, спасибо за предупреждение! Но этот вопрос относится к тестовому заданию, где нужно было найти решение для данного конкретного случая, так что, надеюсь, сгодится. Будь моя воля, я бы добавил столбец с уникальными id для каждой записи, но в условиях задачи такой возможности не дали( Вот и не придумал ничего лучше.