distinct and Over partition

i am working on a query in bigquery and i have the following problem. The query I have is the following:

SELECT DISTINCT (concat(rut,created_at)),*,ROW_NUMBER () OVER (PARTITION BY rut ORDER BY created_at ASC) AS ranking 
FROM table 
WHERE DATE(created_at) <= "2020-12-03" 
order by rut,ranking

the problem is that the “DISCINT” does not eliminate the duplicate data, instead if I eliminate the OVER PARTITION and leave the query as follows, if it eliminates the repeated data.

SELECT DISTINCT (concat(rut,created_at)) ,*
FROM table 
WHERE DATE(created_at) <= "2020-12-03" 
order by rut

clearly it is a problem of how the discint and the OVER PARTITION work, but I can’t find a solution

Answer

you need to do this :

select * ,ROW_NUMBER () OVER (PARTITION BY rut ORDER BY created_at ASC) AS ranking  
from (
SELECT DISTINCT (concat(rut,created_at)),*
FROM table 
WHERE DATE(created_at) <= "2020-12-03" 
) tt
order by rut,ranking

Advertisement

Answer