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

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

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