CODEWITHSUNDEEP
google-bigquery
Tjorriemorrie
Tjorriemorrie

Reputation: 17280

How to do unequality join in BigQuery?

I wish to use < in a JOIN, but get the error:

Error: ON clause must be AND of = comparisons of one field name from each table, with all field names prefixed with table name.

Query:

select
  o1.order_number,
  o1.datetime,
  LAST(o2.datetime),

from spree.orders_unique as o1
join spree.orders_unique as o2
  on o1.order_number = o2.order_number
    and o1.datetime < o2.datetime
group by o1.order_number, o1.datetime

How will I do this?

Upvotes: 3

Views: 1181

Answers (1)

Pentium10
Pentium10

Reputation: 207982

You can vote/star on this issue here: https://code.google.com/p/google-bigquery/issues/detail?id=39

You need to use CROSS JOIN + a WHERE clause to run inequality 

select
  o1.order_number,
  o1.datetime,
  LAST(o2.datetime),

from spree.orders_unique as o1
CROSS JOIN spree.orders_unique as o2
where o1.order_number = o2.order_number
    and o1.datetime < o2.datetime
group by o1.order_number, o1.datetime

Note you might want use CROSS JOIN EACH if this query runs out of resources

Upvotes: 6

Related Questions