SQL Server get latest value by date

Osama_ALASSIRY_25544 0
Viewed 12785

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
sql-server sql database
5 Answers

Note to top answer:

Whilst some may say it is the best answer it will often not be the most efficient query time. In my data the following example is an order of magnitude faster.

SELECT project_id, update_date, update_text FROM projects P WHERE update_date = (SELECT MAX(update_date) FROM projects WHERE project_id = P.project_id)

(If you can have more than one update on a given date then would need max on update_text and group by as in this specific example you would not know which update_text value was valid.)

user_67987040
Related