CODEWITHSUNDEEP
sqlsql-serverdatabase
Osama Al-Maadeed
Osama Al-Maadeed

Reputation: 5695

SQL Server get latest value by date

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

Upvotes: 5

Views: 19174

Answers (5)

Nigel Johnson
Nigel Johnson

Reputation: 11

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

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

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.

Upvotes: 0

SqlZim
SqlZim

Reputation: 38063

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

Upvotes: 12

zambonee
zambonee

Reputation: 1647

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

Upvotes: 1

Raj More
Raj More

Reputation: 48048

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

Upvotes: 1

Related Questions