SELECT MAX PARTITION TABLE

1
votes

I have a table with partition on date(transaction_time), And I have a problem with a select MAX.
I'm trying to get the row with the highest timestamp if I get more then 1 row in the result on one ID.

Example of data:

1. ID = 1 , Transaction_time = "2018-12-10 12:00:00"
2. ID = 1 , Transaction_time = "2018-12-09 12:00:00"
3. ID = 2 , Transaction_time = "2018-12-10 12:00:00"
4. ID = 2 , Transaction_time = "2018-12-09 12:00:00"

Result that I want: 

1. ID = 1 , Transaction_time = "2018-12-10 12:00:00"
2. ID = 2 , Transaction_time = "2018-12-10 12:00:00"

This is my query

SELECT ID, TRANSACTION_TIME FROM `table1` AS T1
WHERE TRANSACTION_TIME = (SELECT MAX(TRANSACTION_TIME)
                            FROM `table1` AS T2
                           WHERE T2.ID = T1.ID )

The error I receive:

Error: Cannot query over table 'table1' without a filter over column(s) 'TRANSACTION_TIME' that can be used for partition elimination

2
google-cloud-platformgoogle-bigquery
Please see stackoverflow.com/q/54135893/6253347 - Elliott Brossard

2 Answers

0
votes

It looks like BigQuery does not the correlated subquery in the WHERE clause. I don't know how to fix your current approach, but you might be able to just use ROW_NUMBER here:

SELECT t.ID, t.TRANSACTION_TIME
FROM
(
    SELECT ID, TRANSACTION_TIME,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TRANSACTION_TIME DESC) rn
    FROM table1
) t
WHERE rn = 1;
0
votes

can be done this way:

SELECT id, MAX(transaction_time) FROM `table1` GROUP BY id;