难道一个qualified子句不是既简单又最好吗?

select userid, my_date, ...
from users
qualify rank() over (partition by userid order by my_date desc) = 1

对于上下文，在Teradata这里一个像样的大小测试运行在17秒与这个合格版本和在23秒与“内联视图”/Aldridge解决方案#1。

SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)
  FROM table
  GROUP BY userid

Select  
   UserID,  
   Value,  
   Date  
From  
   Table,  
   (  
      Select  
          UserID,  
          Max(Date) as MDate  
      From  
          Table  
      Group by  
          UserID  
    ) as subQuery  
Where  
   Table.UserID = subQuery.UserID and  
   Table.Date = subQuery.mDate  

select VALUE from TABLE1 where TIME = 
   (select max(TIME) from TABLE1 where DATE= 
   (select max(DATE) from TABLE1 where CRITERIA=CRITERIA))

难道一个qualified子句不是既简单又最好吗?

select userid, my_date, ...
from users
qualify rank() over (partition by userid order by my_date desc) = 1

对于上下文，在Teradata这里一个像样的大小测试运行在17秒与这个合格版本和在23秒与“内联视图”/Aldridge解决方案#1。

使用ROW_NUMBER()为每个UserId按递减日期分配唯一的排名，然后为每个UserId过滤到第一行(即ROW_NUMBER = 1)。

SELECT UserId, Value, Date
FROM (SELECT UserId, Value, Date,
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) rn
      FROM users) u
WHERE rn = 1;