CODEWITHSUNDEEP
sqlgoogle-bigquery
Mim
Mim

Reputation: 1069

BigQuery, how to use alias in where clause?

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

Upvotes: 4

Views: 8725

Answers (3)

saifuddin778
saifuddin778

Reputation: 7277

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

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

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

Upvotes: 2

Ed Bangga
Ed Bangga

Reputation: 13006

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)

Upvotes: 2

Related Questions