SQL Server get latest value by date

Question

I have a SQL Server table that has project_id int,update_date datetime ,update_text varchar(max)

The table has many updates per project_id. I need to fetch the latest by update_date for all project_id values.

Example:

project_id    update_date    update_text
1             2017/01/01     Happy new year.
2             2017/01/01     Nice update
2             2017/02/14     Happy Valentine's
3             2016/12/25     Merry Christmas
3             2017/01/01     A New year is a good thing

The query should get:

project_id    update_date    update_text
1             2017/01/01     Happy new year.
2             2017/02/14     Happy Valentine's
3             2017/01/01     A New year is a good thing

Answer 1

using top with ties with row_number()

select top 1 with ties
  project_id, update_date, update_text
from projects
order by row_number() over (partition by project_id order by update_date desc)

rextester demo: http://rextester.com/MGUNU86353

returns:

+------------+-------------+----------------------------+
| project_id | update_date |        update_text         |
+------------+-------------+----------------------------+
|          1 | 2017-01-01  | Happy new year.            |
|          2 | 2017-02-14  | Happy Valentine's          |
|          3 | 2017-01-01  | A New year is a good thing |
+------------+-------------+----------------------------+

Answer 2

This query will give you the latest date for each project

Select Project_Id, Max (Update_Date) Max_Update_Date
From MyTable
Group By Project_Id

So join it back to the original table

Select Project_Id, Update_Date, Update_Text
From MyTable
Inner Join
(
    Select Project_Id, Max (Update_Date) Max_Update_Date
    From MyTable
    Group By Project_Id
) MaxDates
On MyTable.Project_Id = MaxDates.Project_Id
And MyTable.Update_Date = MaxDates.Max_Update_Date