Get original and current prices with a subquery in MySQL with PHP

Question

I have a table of ads and another of prices, relative to these ads. When the user modifies a price in the application, a new row is added to the prices table, which includes the price and the date the price was modified.

I need to get a list with all the ads and, for each ad, the first price that was registered, and also the last. I have used a double subquery on the same price table.

Tables

ads

id int	ad_title varchar
1	Potatoes
2	Tomatoes

prices

id int	price decimal	price_timestamp timestamp	ads_id int
1	50	2021-02-16 21:12:36	1
2	5	2021-02-17 21:12:48	1
3	1000	2021-02-17 21:20:40	2
4	900	2021-02-18 13:20:49	2
5	700	2021-02-18 13:20:49	2

Query

    SELECT ads.ad_title, prices_firsts.price AS price_first, prices_currents.price AS price_current
    FROM ads
    LEFT JOIN (
        SELECT *
        FROM prices
        GROUP BY id
        ORDER BY price_timestamp ASC
    ) prices_firsts ON prices_firsts.ads_id = ads.id
    LEFT JOIN (
        SELECT *
        FROM prices
        GROUP BY id
        ORDER BY price_timestamp DESC
    ) prices_currents ON prices_currents.ads_id = ads.id
    GROUP BY ads.id

Esta consulta devuelve lo siguiente en mi servidor local (XAMPP):

ad_title	price_first	price_current
Potatoes	50	5
Tomatoes	1000	700

As you can see the result of the query is correct BUT when it is executed on a server from an external provider (I have tested it in 1&1 IONOS and in Arsys Spain) the results vary practically with each execution. There are times when prices appear in reverse order, sometimes the same price appears in both columns...

What I need?

I need to understand if the problem is in the servers configuration of these providers or if the query is wrong.

I am also open to ideas that you can contribute to get the prices (first and current) in another way, even if it is with another structure in the database.

Answer 1

You could also try using a subquery for min and max date

    select ads.id, p1.price min_price, p2.price max_price,
from ads 
inner join (

    select  ads_id, min(price_timestamp ) min_date, max(price_timestamp )  max_date 
    from prices 
    group by ads_id

 ) t on t.ads_id = ads.id 
 INNER JOIN prices p1 on p1.ads_id = ads.id and p1.price_timestamp = t.min_date
 INNER JOIN prices p2 on p2.ads_id = ads.id and p2.price_timestamp = t.mmaxn_date