# Is it possible to reduce the duplication in this simple query?

I'm new to SQL and so this is probably a stupid question but can I reduce the duplication in this query? My actual query involves more complex expressions. Right now I'm just doing string concatenation from the language I'm using to generate the long query but I think there must be a way to do this with plain SQL?

``````select
x + y as a,
x + z as b,
(x + y) / (x + z) as c
from
integers
order by
z + (x + y) / (x + z) desc
limit
10;
``````

What I'd like:

``````select
x + y as a,
x + z as b,
a / b as c
from
integers
order by
z + c desc
limit
10;

-- Fails with: Query Error: error: column "a" does not exist
``````

Sample table:

``````create table integers as
select x, y, z
from
generate_series(1, 10) x,
generate_series(x, 10) y,
generate_series(y, 10) z;
``````
• Does this answer your question? MySQL "case as column" question in WHERE clause Mar 6, 2021 at 13:53
• Why not `order by c desc`? Mar 7, 2021 at 8:40
• @ypercubeᵀᴹ: Excellent point! Mar 7, 2021 at 12:54
• @ypercubeᵀᴹ this was just an example of the kind of thing I wanted to do :) Mar 7, 2021 at 12:57

Two levels of derived tables (subquery or CTE) to avoid all repeated calculations:

``````SELECT a, b, c
FROM  (
SELECT z, a, b, a / b AS c
FROM (
SELECT z
, x + y AS a
, x + z AS b
FROM   integers
) i
) i
ORDER  BY  z + c DESC
LIMIT  10;
``````

With simple expressions, it won't matter for performance.
What might matter with repeating more expensive expressions is that all involved functions have (correct!) volatility `STABLE` or `IMMUTABLE`. See:

• Thanks I understand how this one would avoid all repeated calculations. For simple expressions, can this actually be slower than my original query? Is Postgres (and other databases) smart enough to optimize this? (If I write this kind of code in a normal programming language, creating intermediary data structures, it'll be terribly inefficient.) Mar 7, 2021 at 12:53
• @user225326: The Postgres query planner / optimizer rearranges things quite a bit, and verbose queries often end up in a very simple query plan. Compare `EXPLAIN` output to see resulting differences. And the ultimate judge will be effective execution times. Mar 7, 2021 at 12:59