1

I am not good at SQL, I wanted to take MAX using partition by in the following query, but when I use the same query without where clause of that max drive column it says that column does not exist but if I remove the column from where I can see in select the same column is present.

select 
MAX(case when total_split_count = 0 or total_split_count is null then total_split_count  else 1 end) OVER (PARTITION BY ia.col1,ia.col2,ia.col3,ia.col4,ia.col5,ia.col6) as bb
from audits.tbl_name ia
where bb = 1

ERROR: column "bb" does not exist Position: 304

where bb = 1 ^ 1 statement failed.

but the query runs with where clause:

select 
MAX(case when total_split_count = 0 or total_split_count is null then total_split_count  else 1 end) OVER (PARTITION BY ia.col1,ia.col2,ia.col3,ia.col4,ia.col5,ia.col6) as bb
from audits.tbl_name ia

Note: I created that column at run time through "as".

Indrajeet Gour
  • 4,020
  • 5
  • 43
  • 70
  • I think you need `having` instead of `where`to filter aggregate result – M Khalid Junaid Aug 14 '20 at 07:44
  • I got the answer, I wanted to use the field(the new one) before where clause but where operation performs before the select.. that is why that particular column is now available for whereas it is not calculated before.. – Indrajeet Gour Aug 14 '20 at 07:47

1 Answers1

4

The alias defined in select clause in not visible in where clause. Use

select * from (select ... as bb from audits.tbl_name ia) x where bb = 1

or CTE:

with x as (select ... as bb from audits.tbl_name ia) select * from x where bb = 1
Tomáš Záluský
  • 10,735
  • 2
  • 36
  • 64