BigQuery not allow Subselect in select clause -

i have sample 2 table:

table point_status: rank_point  status 9       excellent 5       3       bad 0       fail  table student_point: student     point student   8 student b   9 student c   5 student d   4 

how create query on bigquery status of student (with point >= rank_point)?

note:- bigquery not allow subselect in select clause.

note: bigquery not allow subselect in select clause.

it does support standard sql - see enabling standard sql

with point_status (   select 9 rank_point, 'excellent' status union   select 5 rank_point, 'good' status union   select 3 rank_point, 'bad' status union   select 0 rank_point, 'fail' status ), student_point (   select 'student a' student, 8 point union   select 'student b' student, 9 point union   select 'student c' student, 5 point union   select 'student d' student, 4 point ) select    student, point,   (select status      point_status      rank_point <= point      order rank_point desc     limit 1   ) status student_point 

meantime, if bound legacy sql, see below

select student, point, status (   select      student, point, status,      row_number() over(partition student order rank_point desc) pos   student_point   cross join point_status   point - rank_point >= 0 ) pos = 1