is there a way in sql to use derived columns in SELECT statement to create more dependent columns without using temp tables?

Question

Say I have something like below for example. I think it is impossible to use the newly created rowid as a condition for a case when var or am I wrong? I could create a temp table but is there another way I can use rowid as a condition for a new case when variable, preferably without using temp tables?

select 
  * from (select ROW_NUMBER()OVER(partition by id ORDER BY id) as rowid ,
 case when rowid > 1 then 'a' else 'b' end as newvar
from tableA) as A

You can't reference columns by their alias in the same `SELECT`. You would need to define the column in a subquery or CTE first, and then reference it in the outer query/final `SELECT`. — Thom A, Apr 23 '20 at 17:59
the second question you referenced answered my question perfectly, thank you — biostatguy12, Apr 23 '20 at 18:06

score 0 · Answer 1 · answered Apr 23 '20 at 18:00

0

Your query should be like below :

SELECT * FROM (
select CASE ROW_NUMBER()OVER(partition by id ORDER BY id) > 1 then 'a' else 'b' end as newvar
from tableA ) AS A

answered Apr 23 '20 at 18:00

Amira Bedhiafi

8,088
6
24
60

is there a way in sql to use derived columns in SELECT statement to create more dependent columns without using temp tables?

1 Answers1