MySQL как получить 2-ую самую последнюю запись в связанной таблице в поисковом запросе

Question

У меня есть MySQL DB с двумя связанными таблицами Products и Price History. Они связаны полем productID. Каждый раз, когда цена продукта изменяется, я создаю новую запись истории. Последняя запись истории для продукта имеет самую актуальную цену. Я также храню текущую цену в таблице продуктов. Я хочу создать отчет, в котором я могу получить 2-ую последнюю запись в истории цен, чтобы сравнить текущую и последнюю цену. Я попробовал запрос sql ниже, и он возвращает последнюю запись истории цен, т.е. текущую цену. Как я могу получить 2-ую самую последнюю запись истории цен? HistoryID будет выше для более новых записей, так как это Автоинкремент, и updateTime ценовой истории будет более поздним для более новых записей, так что это может быть способом сортировки. Спасибо!

SELECT 
  product.code, product.currentPrice, priceHistory.price, 
  product.url, product.manuID, product.lastSeenTime,
  priceHistory.updateTime, product.dateAdded, 
  priceHistory.historyID 
FROM product, priceHistory 
WHERE product.idProduct = priceHistory.productID 
GROUP BY priceHistory.productID 
HAVING count(*) > 1 
ORDER BY `product`.`lastSeenTime` DESC

Answer 1

Вы можете использовать ROW_NUMBER() оконная функция для назначения нумерации строк в соответствии с любым порядком на лету. Как только вы это сделаете, вы можете просто фильтровать по этому номеру.

Например:

with
h as (
  select *,
    row_number() over(partition by productid order by updatetime desc) as rn
  from pricehistory
)
select
  p.code,
  p.currentprice,
  h.price,
  p.url,
  p.manuid,
  p.lastseentime,
  h.updatetime,
  p.dateadded,
  h.historyid
from product p
left join h on h.productid = p.productid and h.rn = 2

РЕДАКТИРОВАТЬ:

Если вы не можете использовать CTE, вы можете переписать запрос, используя табличное выражение, как в:

select
  p.code,
  p.currentprice,
  h.price,
  p.url,
  p.manuid,
  p.lastseentime,
  h.updatetime,
  p.dateadded,
  h.historyid
from product p
left join (
  select *,
    row_number() over(partition by productid order by updatetime desc) as rn
  from pricehistory
) h on h.productid = p.productid and h.rn = 2