MySQL how to get 2nd most recent record in linked table in search query [closed]

I have a MySQL DB with two linked tables Products and Price History. They are linked by a productID field. Each time the price of a product changes I create a new history record. The latest history record for a product has the most current price. I also store the current price in the product table. I want to run a report where I can retrieve the 2nd last price history record so I can compare the current and last price. I tried the sql query below and that returns the latest price history record i.e current price. How can I get the 2nd most recent price history record? The historyID will be higher for newer records as it is Auto Increment and price history updateTime will be more recent for newer records also so that could be a way to sort. Thanks!

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

You can use the ROW_NUMBER() window function to assign numbering to rows according to any ordering on the fly. Once you do that, then you can simply filter by that number.

For example:

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

EDIT:

If you cannot use CTEs, then you can rewrite the query by using a table expression, as in:

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