Date field as version - 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

SQL Server Development (2000)

Date field as version

Author

Topic

inrsence
Starting Member

48 Posts

Posted - 2001-09-13 : 16:02:48

Hi,

I'm trying to write this query and I can't seem to get it to work. I have a table that stores various attributes and maintains a DateCreated field to track the date.

I would like to query out the most recent values for the various attributes. The goal is that later on I can re-create the mix of attributes for a specific date range.

I was originally contemplating having a separate table to store the archive and one for the latest values.. does that approach make more sense? I read somewhere here that someone was using this approach successfully using triggers on the main table.

I tried this:


SELECT 	TID,AttributeID,AttrValue
FROM	Data d
WHERE	BID = 1 AND
	d.DateCreated = (SELECT Max(d2.DateCreated) FROM Data d2 WHERE d2.TID = d.TID AND d2.AttributeID = d.AttributeID)
GROUP BY TestID,AttributeID,AttrValue,DateCreated

This seems to return all values still though.

I also tried:


SELECT 	TID,AttributeID,AttrValue,Max(DateCreated) AS MaxCreate
FROM	Data
WHERE	BID = 1
GROUP BY TestID,AttributeID,AttrValue

I also tried using HAVING like this:


SELECT 	TID,AttributeID,AttrValue
FROM	Data d
WHERE	BID = 1
GROUP BY TestID,AttributeID,AttrValue,DateCreated
HAVING DateCreated = Max(DateCreated)

Thanks in advance for any guidance,
Greg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources