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

SqlZim · Accepted Answer · 2017-09-13 17:33:23Z

12

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 |
+------------+-------------+----------------------------+

edited Sep 13, 2017 at 17:33

answered Sep 13, 2017 at 17:30

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Simon Over a year ago

Beat me to it! This is the correct answer. Partition by project_id and ordering by date desc is the best way to answer this kind of multiple id/audit type of data.

Raj More · Accepted Answer · 2017-09-13 17:28:41Z

1

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

answered Sep 13, 2017 at 17:28

Raj More

48.2k34 gold badges139 silver badges199 bronze badges

Comments

zambonee · Accepted Answer · 2017-09-13 17:30:42Z

1

You can find the MAX(date) like:

SELECT * FROM [table] 
INNER JOIN (SELECT project_id, date = MAX(update_date) FROM [table] GROUP BY project_id) AS a
ON [table].project_id = a.project_id AND update_date = date

or you can use ROW_NUMBER() like:

SELECT * FROM (
SELECT *, rownum = ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY 
update_date DESC) FROM [table]
) AS a WHERE rownum = 1

answered Sep 13, 2017 at 17:30

zambonee

1,64713 silver badges17 bronze badges

Comments

Nigel Johnson · Accepted Answer · 2019-08-06 22:29:11Z

1

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.)

answered Aug 6, 2019 at 22:29

Nigel Johnson

112 bronze badges

Comments

Ilyes · Accepted Answer · 2017-09-13 17:46:15Z

0

You can do it as:

with CTE as(
SELECT project_id,    MAX(update_date) update_date
FROM YourTable
GROUP BY project_id
    )

SELECT  cte.project_id,    cte.update_date , max(t.update_text) update_text
FROM YourTable T inner join CTE on T.project_id = CTE.project_id
group by cte.project_id,    cte.update_date ;

Demo.

edited Sep 13, 2017 at 17:46

answered Sep 13, 2017 at 17:28

Ilyes

15k5 gold badges31 silver badges61 bronze badges

1 Comment

Simon Over a year ago

This would give you an error, you have MAX(update_date) and GROUP BY project_id but update_text is missing an aggregation.

Collectives™ on Stack Overflow

SQL Server get latest value by date

5 Answers 5

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related