Posted by Chandra on 05/27/05 07:23

Here is the view that you are lookig for.

Please let me know if you require anything else

CREATE VIEW Latest_Data
AS
SELECT device_id, [datetime], name, value
FROM devicedata
INNER JOIN
(
SELECT device_id, max([datetime]) [datetime], name
FROM devicedata
GROUP BY device_id, name
) as DerTab
WHERE
devicedata.device_id = DerTab.device_id AND
devicedata.[datetime] = DerTab.[datetime] AND
devicedata.name = DerTab.name
GO


best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***

• Next in forum: Re: What's the difference? Unque Constraint and unique index, etc.?
• Prev in forum: Re: Trigger Error
• Thread view: Re: Need some help with design of tables/views

[Reply to this message]