Is it possible in SQL to create aliases in a where clause and use it in a select?

Question

MySQL

For example, there is a Prices table with prices. all ids in the table are sorted in ascending order.

You need to get price ranges where the maximum price is greater than a certain number.
Here is an example request;

SELECT p1.id as id1, p2.id as id2, (SELECT MAX(price) FROM Prices p3 WHERE p3.id >= id1 AND p3.id <= id2 ) 
FROM Prices as p1, Prices as p2
WHERE (SELECT MAX(price) FROM Prices p3 WHERE p3.id >= id1 AND p3.id <= id2 ) > 150

Those. From the query it is clear that we have two identical constructs. Is it possible to get rid of duplication? Those. in the above example, the construction is duplicated

(SELECT MAX(price) FROM Prices p3 WHERE p3.id >= id1 AND p3.id <= id2 )

Is it possible, for example, to somehow set an alias for the construction from where and use it in select?
Those. Ideally, I would like to get the following:

SELECT p1.id as id1, p2.id as id2, subq
FROM Prices as p1, Prices as p2
WHERE (SELECT MAX(price) FROM Prices p3 WHERE p3.id >= id1 AND p3.id <= id2 ) as subq> 150

But that doesn't work.
Can you please tell me how to get rid of duplication?
I use postgresql, but if there are similar problems in other subds, it would be interesting to see a solution

Answer 1

You need to get price ranges where the maximum price is greater than a certain number.

I think you really need:
And answering the question in the forehead - lateral join'om to transfer the subquery to the from section and that's it.

Answer 2

In the ANSI SQL standard and ISO SQL standard, the value that is associated with a column alias does not need to be available until the ORDER BY clause is executed.
that's the standard.
but for those who wish, there are examples of circumventing restrictions through cte. although from the point of view of query optimality, this does not provide any advantages.