Sorting data by calculation from another table and paging?

Question

MySQL

Hello, there are tables:
currency


exchange_rates


You need to get the rate of the first day, for the selected period - first , and the last day - last . to calculate the difference. Then {last} - {first} = {X}. And for this {X} do the sorting of the selection.

Now I have the difference calculated by the backend and simply displayed in the corresponding column.

If someone has come across something similar, and there is time to share their thoughts and experience in solving similar problems, albeit with basic examples, I would be very grateful for such an experience.

The only solution that I see is to collect all the currencies, do the calculations on the backend, sort and display what I need with pagination. But it would work with a small number of currencies. And I see no reason to work with something that, as a result, will not be displayed to the user. It seems to me that this is absolutely not a rational decision, please share your experience and advice on how you make such samples.

Probably crookedly described the task, but I hope nevertheless that is clear. If necessary, I am ready to pay your time for explaining how to work with this.

Answer 1

You need to get the rate of the first day, for the selected period - first, and the last day - last. to calculate the difference. Then {last} - {first} = {X}. And for this {X} do the sorting of the selection.

Yes, the request is trivial!

SELECT DISTINCT 
       coin_uuid,
       FIRST_VALUE(price) OVER (PARTITION BY coin_uuid ORDER BY last_updated ASC) firstprice,
       FIRST_VALUE(price) OVER (PARTITION BY coin_uuid ORDER BY last_updated DESC) lastprice
FROM exchange_rates
ORDER BY coin_uuid, lastprice - firstprice