Most recent data point?

[Michael Bray]

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(TimeCollect ed) AS MaxTime
FROM @Data
GROUP BY DataID
) dt ON (d.DataID=dt.Da taID) AND (d.TimeCollecte d=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

[Peter Bromberg [C# MVP]]

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(TimeCollect ed) AS MaxTime
FROM @Data
GROUP BY DataID
) dt ON (d.DataID=dt.Da taID) AND (d.TimeCollecte d=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