Returning TimeStatmp & MAX(value) for Multiple Tags

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)

Returning TimeStatmp & MAX(value) for Multiple Tags

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-10 : 08:48:57

Ed writes "I have a realtime History Table: TagG, Value, TimeDateStamp

I Need SQL Server Query result to return the MAX(value) of each Tag recorded for a particular Date.

Strait forward approach:

SELECT Tag, TimeDateStamp, MAX(Value) as 'MaxValue'
FROM dbo.History
WHERE TimeDateStamp BETWEEN date1 AND date2
ORDER BY Tag

Doesn't work!

In MS ACCESS:

SELECT [tb1].[Tag], [tb1].['MaxValue'], First([History].[DateTimeStamp]) AS FirstOfDateTimeStamp
FROM [SELECT History.Tag, Max(History.Value) AS ['MaxValue']
FROM History
WHERE (((History.DateTimeStamp) Between date1 And date2))
GROUP BY History.Tag ]. AS tb1
INNER JOIN History
ON ([tb1].['MaxValue']=[History].[Value]) AND ([tb1].[Tag]=[History].[Tag])
GROUP BY [tb1].[Tag], [tb1].['MaxValue']
ORDER BY [tb1].[Tag];

This works in ACCESS. But not in SQL Server.:(

Any ideas?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-09-10 : 08:49:48

Have you tried:

SELECT Tag, TimeDateStamp, MAX(Value) as 'MaxValue'
FROM dbo.History
WHERE TimeDateStamp BETWEEN date1 AND date2
GROUP BY Tag, TimeDateStamp
ORDER BY Tag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources