在Presto SQL的where子句中使用Max（）

在子查詢中選擇多個(gè)值無效，需要使用標(biāo)量子查詢選擇單個(gè)值：

-- sample data
WITH dataset (ID, Desc, progress, updated_time) AS (
    VALUES 
(1, 'abcd', 'planned',  timestamp '2022-04-20 10:00'),
(1, 'abcd', 'planned',  timestamp '2022-04-25 12:00'),
(1, 'abcd', 'in progress',  timestamp '2022-04-26 16:00'),
(1, 'abcd', 'in progress',  timestamp '2022-05-04 11:00'),
(1, 'abcd', 'in progress',  timestamp '2022-05-06 12:00'),
(1, 'abcd', 'in progress',  timestamp '2022-05-07 12:00'),
(2, 'abcd', 'in progress',  timestamp '2022-05-04 11:00'),
(2, 'abcd', 'in progress',  timestamp '2022-05-06 12:00')
) 

--query
select  id, Desc, progress, updated_time
from dataset o
where updated_time = (select max(updated_time) from dataset i where i.id = o.id)

或使用max窗口函數(shù)和subselect的類似方法：

--query
select  id, Desc, progress, updated_time
from (
    select *,  max(updated_time) over (partition by id) max_time
    from dataset
)
where max_time = updated_time

或者只是使用row_number：

select  id, Desc, progress, updated_time
from 
(
    select *,  
        row_number() over(partition by id order by updated_time desc) rank
    from dataset
)
where rank  = 1

Output: