Maximum of many data entries

Question

I have a database with the following setup

|ID | project-id  |  begind_date    |    
| 1 | A01         |  2007           |    
| 2 | A01         |  2011           |
| 3 | A01         |  2011           |    
| 4 | A02         |  2010           |    
| 5 | A02         |  2011           |    
| 6 | A03         |  2013           |

What I am looking to show is the ID of of the highest begin date of each project like, the result would be:

|ID | project_id  |  begind_date    |
| 2 | A01         |  2011           |
| 3 | A01         |  2011           |
| 5 | A02         |  2011           |
| 6 | A03         |  2013           |

I tired MAX(begind_date) and GROUP BY project_id but couldn't get the exact result I want.

SQL I tried to get this done was:

SELECT ID, project_id, MAX(begin_date) maxDate FROM table_name GROUP BY ID,  project_id;

Much appreciated :)

Answer 1

A simple way is a correlated subquery:

select t.*
from t
where t.begind_date = (select max(t2.begind_date)
                       from t t2
                       where t2.project_id = t.project_id
                      );