How to get the last row of every rank in different groups

Question

I have this table that shows the same id with different types and every type have the same rank that ordered by start date.

id	type	start date	rank
111	15	1/1/22	1
111	15	1/3/22	1
111	15	2/04/22	1
111	23	1/02/22	2
111	23	1/3/22	2
111	25	16/03/22	3

I want to get table that will show only the last row for every rank

id	type	start date	rank
111	15	2/04/22	1
111	23	1/3/22	2
111	25	16/03/22	3

Answer 1

use group by and get result

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

Answer 2

You can use ROW_NUMBER() like this:

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 Fiddle

Note: You can add any combination of columns after partition by like "start date", "rank" and also after order by

Answer 3

We can use FIRST_VALUE with DISTINCT in a subquery and use ROWNUM to add the rank column:

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;

Beware that this query will - like the queries from the other answers, too - fail in case the date column can be NULL! To avoid that, we can add an IGNORE NULLS clause:

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;

I don't know if that "rank" column in your description already holds the correct row number information and can safely be used for that or if this is not possible or too risky.

If it can be used for that, there is no need for a subquery to fetch the row number. Again the two options with or without ignoring NULL values:

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";

OR

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";