BigQuery, how to use alias in where clause?

Question

I have this small query, BigQuery don't recognise the alias maxdate, I am trying to return the table but only for that last date

WITH
  maxdate AS (
  SELECT
    MAX(DATE(SETTLEMENTDATE))
  FROM
    `biengine-252003.aemo2.daily`)
SELECT
  *
FROM
  `biengine-252003.aemo2.daily`
WHERE
  DATE(SETTLEMENTDATE)= maxdate

Answer 1

You can also do something like:

with maxdate as (
   select 
      max(date(SETTLEMENTDATE)) as mx
   from
  `biengine-252003.aemo2.daily`
)
select 
   d.* 
from `biengine-252003.aemo2.daily` d
left join maxdate on 1=1
where date(d.SETTLEMENTDATE) = maxdate.mx

Answer 2

I am trying to return the table but only for that last date

Meantime, more effective way to achieve the goal is

#standardSQL
SELECT * EXCEPT(isLastDate) 
FROM (
  SELECT *, DATE(SETTLEMENTDATE) = MAX(DATE(SETTLEMENTDATE)) OVER() isLastDate
  FROM `biengine-252003.aemo2.daily`
)
WHERE isLastDate

Answer 3

You are trying to get the value of maxdate from your table maxdate. Add alias to your column and use this.

WITH
  maxdate AS (
  SELECT
    MAX(DATE(SETTLEMENTDATE)) as mx
  FROM
    `biengine-252003.aemo2.daily`)
SELECT
  *
FROM
  `biengine-252003.aemo2.daily`
WHERE
  DATE(SETTLEMENTDATE) = (SELECT mx FROM maxdate LIMIT 1)