Is it possible to avoid duplicate subqueries?

Question

MySQL

There is a table that stores, for example, prices for a certain currency pair. the table looks like this:

You need to get a selection, where the first column will contain the price from a certain slice of this table, and the second column will also contain the price from this slice, but more than the first one.
That is, the request will look like this:

SELECT part_1.price, part_2.price 
FROM (SELECT * FROM prices WHERE id > 1000 AND id < 2000000) AS part_1, (SELECT * FROM prices WHERE id > 1000 AND id < 2000000) AS part_2
WHERE part_1.price < part_2.price

It can be seen from the query that we have 2 identical subqueries. But this, as I understand it, is not according to "feng shui", because. we receive the same data several times.
Hence the question, is it possible to rewrite this query so that these subqueries are not duplicated? And if so, how?
The task is hypothetical, the real task is more complicated and there will be many more duplicates of these. Ie, the given example resulted that the problem was clear.
That is, you need to get an answer exactly for duplicates, how to get rid of them I
use postgresql

Answer 1

GROUP BY + HAVING