0

Has anyone else experienced this issue?

I have a query:

SELECT * FROM `table-in-big-query` AS t1
INNER JOIN ( SELECT unique_Field, MAX(Upload_Datetime) AS latest_Upload_Datetime FROM `table-in-big-query` GROUP BY unique_Field)
AS t2 ON (t1.unique_Field= t2.unique_Field AND t1.Upload_Datetime = t2.latest_Upload_Datetime)

This returns the most recent rows of uploaded data for the unique_Field.

When I run this in On Tableau Online in Tableaus Custom SQL Query I get now Rows in the Response. enter image description here

I also checked it in the Tableau Prep Builder Application just to make sure it wasn't Tableau Online..

I have checked if the connection works by just running

SELECT * from `table-in-big-query`

And it does so I'm not sure where the Mismatch is...

beautysleep
  • 153
  • 1
  • 9
  • you should use a INNER JOIN, as every row in the right subquery must be in the left – nbk Jul 03 '23 at 13:44
  • Yes INNER JOIN is what I had started with and when I was just checking around what might be causing the issue I changed it. – beautysleep Jul 03 '23 at 13:48
  • 1
    the try row_number instead of a join, and remove every row_number that is not 1(ok this would only return 1 per partition, this is only to test the data `SELECT *, ROW_NUMBER() OVER(PARTITION BY unique_Field ORDER BY Upload_Datetime DESC) rn from `table-in-big-query` – nbk Jul 03 '23 at 13:52

1 Answers1

0

Thank you to nbk for providing a solution.

I do not have an answer as to why this works but the previous option didn't.

The solution used was:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY unique_FieldORDER BY Upload_Datetime DESC)
AS temp_row_number
from `table-in-big-query` )
WHERE temp_row_number = 1
beautysleep
  • 153
  • 1
  • 9