Select Most Recent Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select Most Recent Records

Author

Topic

RDee
Starting Member

2 Posts

Posted - 2005-03-23 : 14:31:05

I have a table of logged values with the following cols:

PointID (int)
Value (real)
LoggedTimeStamp (datetime)

Any given point will have multiple rows in this table, each with a unique LoggedTimeStamp and its Value.

I trying to find the simplest way to Select only the most recent row for each PointID where PointID in (2, 4, 5,.... list of some PointID's). Would like to have returned only one row for each point in list, and that row be the most recent timestamp that particular point.

Thanks for any suggestions,

RDee

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-23 : 14:40:19

The query to get the rows:
SELECT PointID, MAX(LoggedTimeStamp)
FROM SomeTable
WHERE PointID IN (2,4,5,...)
GROUP BY PointID

Then to get the Value, you use the above as a derived table and join to the table:



SELECT a.PointID, b.Value, a.LoggedTimeStamp
FROM
(
	SELECT PointID, MAX(LoggedTimeStamp) AS LoggedTimeStamp
	FROM SomeTable
	WHERE PointID IN (2,4,5,...)
	GROUP BY PointID
) a
INNER JOIN SomeTable b
ON a.PointID = b.PointID AND a.LoggedTimeStamp = b.LoggedTimeStamp

Tara

RDee
Starting Member

2 Posts

Posted - 2005-03-24 : 10:32:24

Thanks for your help. Your suggestion worked very well.

RDee

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources