sql - 编译语句时出错：FAILED: SemanticException [Error 10002] - MAKE SELECT WITH MAX ON HIVE

Question

我正在尝试执行此选择以仅返回最近插入的记录

我正在使用 HIVE

co_junta_comer 和 co_informacao 可以重复，但需要最近的记录

select *
from reference_data.reference_data_novajunta A
where A.ts_inclusao IN 
  (select max(row2.ts_inclusao)
   from reference_data.reference_data_novajunta row2 
   where A.co_junta_comer = row2.co_junta_comer
     AND row2.co_informacao = A.co_informacao)

我收到错误

Error while compiling statement: FAILED: SemanticException [Error 10002]: line 4:28 Invalid column reference 'co_junta_comer'`

标签： sqlhivehiveql

Answer 1

使用row_number()：

select rdn.*
from (select rdn.*,
             row_number() over (partition by co_junta_comer, co_informacao order by ts_inclusao desc) as seqnum
      from reference_data.reference_data_novajunta rdn
     ) rdn
where seqnum = 1;