471,347 Members | 1,741 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,347 software developers and data experts.

Most recent data point?

I have a table that stores data points for several different data sources.

The general format is:

DECLARE @Data TABLE
(
DataID int,
TimeCollected int,
DataValue decimal(9,9)
)

INSERT @Data VALUES (1, 1000, 0.75)
INSERT @Data VALUES (1, 1001, 0.69)
INSERT @Data VALUES (1, 1002, 0.77)
INSERT @Data VALUES (2, 1000, 0.45)
INSERT @Data VALUES (2, 1001, 0.49)
INSERT @Data VALUES (2, 1002, 0.53)
INSERT @Data VALUES (3, 1000, 0.50)
INSERT @Data VALUES (3, 1001, 0.35)
INSERT @Data VALUES (3, 1002, 0.41)

I want to pull the most recent datapoint for each of the DataIDs. I can do
it like this:

SELECT d.*
FROM @Data d
INNER JOIN
(
SELECT DataID, MAX(TimeCollected) AS MaxTime
FROM @Data
GROUP BY DataID
) dt ON (d.DataID=dt.DataID) AND (d.TimeCollected=dt.MaxTime)

Is there a more efficient way than this? I may have hundreds of different
DataIDs with tens or hundreds of thousands of data points for each DataID.

Thanks!

-mdb
Jan 13 '06 #1
1 1584
Give your TABLE variable a Primary Key IDENTITY column. Then, you can select
Max(PK) from it.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Michael Bray" wrote:
I have a table that stores data points for several different data sources.

The general format is:

DECLARE @Data TABLE
(
DataID int,
TimeCollected int,
DataValue decimal(9,9)
)

INSERT @Data VALUES (1, 1000, 0.75)
INSERT @Data VALUES (1, 1001, 0.69)
INSERT @Data VALUES (1, 1002, 0.77)
INSERT @Data VALUES (2, 1000, 0.45)
INSERT @Data VALUES (2, 1001, 0.49)
INSERT @Data VALUES (2, 1002, 0.53)
INSERT @Data VALUES (3, 1000, 0.50)
INSERT @Data VALUES (3, 1001, 0.35)
INSERT @Data VALUES (3, 1002, 0.41)

I want to pull the most recent datapoint for each of the DataIDs. I can do
it like this:

SELECT d.*
FROM @Data d
INNER JOIN
(
SELECT DataID, MAX(TimeCollected) AS MaxTime
FROM @Data
GROUP BY DataID
) dt ON (d.DataID=dt.DataID) AND (d.TimeCollected=dt.MaxTime)

Is there a more efficient way than this? I may have hundreds of different
DataIDs with tens or hundreds of thousands of data points for each DataID.

Thanks!

-mdb

Jan 13 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Nova's Taylor | last post: by
3 posts views Thread by Suresh Kumaran | last post: by
6 posts views Thread by HandersonVA | last post: by
1 post views Thread by getyourbiglobster | last post: by
reply views Thread by Ronak mishra | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.