SQL - How to get the most recent entry with common field?

Question

I have a table:

id  | id2  | last_attempt
1   | 100  | 201
2   | 100  | 202
3   | 101  | 203
4   | 102  | 204
5   | 100  | 205
6   | 102  | 206
7   | 101  | 207

I want to end up with only one instance of "id2" and it must be the one with the highest "id":

id  | id2  | last_attempt
5   | 100  | 205
6   | 102  | 206
7   | 101  | 207

So I assume that I need to group by id2 and the do some kind of subquery to only include the latest items.

Any idea how to do this quickly?

Answer 1

Use MAX to get the latest value

SELECT id,id2,MAX(last_attempt) FROM table GROUP BY id2;

Answer 2

You can use a self join to achieve this:

select a.*
from your_table a
left join your_table b on a.id2 = b.id2
    and a.id < b.id
where b.id2 is null;

Demo

Another approach is through aggregation:

select a.*
from t a join (
    select id2, max(id) id
    from t
    group by id2
) b on a.id2 = b.id2 and a.id = b.id

Demo 2

Answer 3

select id, id2, max(last_attempt)
from table_name
group by id2