how to use a column alias in the ON clause in Bigquery-CodePudding

with T2 as
(select C5,C6 from T6) 
   
SELECT C1,C2,C3,
    FROM 
    (
        SELECT C2,C3,C4,C5
        firstvalue(C4) over (partition by C2,order by C3) as C1
        FROM T1
     ) T3
   left join T2
   on left(T1.c1,3) = T2.C5

I am trying to join based on alias (derived column) similar to the above query, what would be the solution If I want to join using alias in Bigquery, please advise.

getting error like C1 not found in T1.

CodePudding user response：

SELECT u.id, u.distance as distance
FROM USERS u
INNER JOIN
(
    SELECT user_id,
    min(10 * 10) as mindistance
    FROM Location
    GROUP BY user_id
 ) L ON u.id = L.user_id AND u.distance = L.mindistance

CodePudding user response：

Updated questions is so different from original one that I need to post it as a separate answer

The were quite a number of things to fix - just compare with one you posted to see the differences

with T2 as (
  select C5,C6 from T6
) 
SELECT C1,C2,C3,
FROM (
  SELECT C2,C3,C4,C5,
  first_value(C4) over (partition by C2 order by C3) as C1
  FROM T1
) T3
left join T2
on left(T3.c1, 3) = T2.C5