使用group by并获取结果

select id,type,MAX(startdate) AS startdate,rank 
from TABLENAME
GROUP BY id,type,rank

您可以像这样使用ROW_NUMBER()：

select *
from (
    select "id", "type", "start date", "rank", 
        row_number() over (partition by "type" order by "start date" desc) rnk
    from Table1 ) t
where t.rnk = 1

SQL小提琴
注意：您可以在partition by（如"start date", "rank"）之后以及order by之后添加任意列组合

我们可以在子查询中使用FIRST_VALUE和DISTINCT，并使用ROWNUM添加rank列：

SELECT "id", "type", startdate, ROWNUM AS rank
FROM
(SELECT DISTINCT "id", "type",
FIRST_VALUE("start date") OVER 
(PARTITION BY "type" ORDER BY "start date" DESC) AS startdate
FROM Table1) groupedData;

请注意，如果date列可以是NULL，则此查询将失败（与其他答案中的查询一样）！为了避免这种情况，我们可以添加一个IGNORE NULLS子句：

SELECT "id", "type", startdate, ROWNUM AS rank
FROM
(SELECT DISTINCT "id", "type",
FIRST_VALUE("start date") IGNORE NULLS OVER 
(PARTITION BY "type" ORDER BY "start date" DESC 
 ROWS BETWEEN unbounded preceding AND unbounded following) AS startdate
FROM Table1) groupedData;

我不知道您描述中的“ranking”列是否已经包含了正确的行号信息，是否可以安全地用于此目的，或者这是否不可能或风险太大。
如果可以使用它，就不需要子查询来获取行号。同样，有两个选项可以忽略或不忽略NULL值：

SELECT DISTINCT "id", "type",
FIRST_VALUE("start date") OVER
(PARTITION BY "type" ORDER BY "start date" DESC ) AS startdate,
 "rank"
FROM Table1
ORDER BY "id", "type", "rank";

或

SELECT DISTINCT "id", "type",
FIRST_VALUE("start date") IGNORE NULLS OVER 
(PARTITION BY "type" ORDER BY "start date" DESC 
 ROWS BETWEEN unbounded preceding AND unbounded following) AS startdate,
 "rank"
FROM Table1
ORDER BY "id", "type", "rank";