CODEWITHSUNDEEP
sqlgoogle-bigquery
jason m
jason m

Reputation: 6835

google bigquery inner join not behaving as expected

I have a table named T with the following structure:

Row date    a   b   c   d   e   f   g    
1   2.0051012E7 4.0 5.0 1.0 0.9 21.0    2.0170716E7 0.8
2   2.0131101E7 1.0 5.0 0.0 1.0 21.0    2.0170716E7 0.6  
3   2.0060908E7 3.0 5.0 0.0 1.0 21.0    2.0170716E7 0.7

and I have the following query:

select * from 
(SELECT date,max(a) as w FROM [T] 
group by date
order by date asc) as tableA
 inner  join
(select date,b from 
 [T]   ) as tableB
on tableB.date=tableA.date and tableB.b=tableA.w
order by a.date asc

Yet my results have:

Row tableA.date a   tableB.date b    
1   2.0040329E7 1.0 2.0040329E7 1.0  
2   2.0040329E7 1.0 2.0040329E7 1.0  
3   2.0040329E7 1.0 2.0040329E7 1.0  
4   2.0040329E7 1.0 2.0040329E7 1.0

Why do I have repeating rows? Isn't this what an inner join should eliminate?

Upvotes: 0

Views: 380

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

I would recommend using BigQuery Standard SQL instead so there will be less confusions

I have results that map date to a max value. i then want to join to those unique dates and values the other criteria from those dates

Try below for BigQuery Standard SQL 

#standardSQL
SELECT entry.* 
FROM (
  SELECT ARRAY_AGG(row ORDER BY a DESC LIMIT 1)[OFFSET(0)] AS entry
  FROM `yourProject.yourDataset.yourTable` row 
  GROUP BY date
)
-- ORDER BY date

you can test it with dummy data as below 

#standardSQL
WITH T AS (
  SELECT 2.0051012E7 AS date, 5.0 AS a, 5.0 AS b, 1.0 AS c, 0.9 AS d, 21.0 AS e, 2.0170716E7 AS f, 0.8 AS g UNION ALL
  SELECT 2.0131101E7, 1.0, 5.0, 0.0, 1.0, 21.0, 2.0170716E7, 0.6 UNION ALL  
  SELECT 2.0060908E7, 3.0, 5.0, 0.0, 1.0, 21.0, 2.0170716E7, 0.7 
)
SELECT entry.* 
FROM (
  SELECT ARRAY_AGG(row ORDER BY a DESC LIMIT 1)[OFFSET(0)] AS entry
  FROM `T` row 
  GROUP BY date
)
ORDER BY date

Upvotes: 1

Related Questions