PDA

View Full Version : Unique retrieval from timestamp



ben.oates
06-13-2008, 08:57 AM
Hi all,

I am sure this has been covered before but I couldn't find anything that was either applicable to me or I could actually understand, so I apologise for duplication.

I am creating a view of a table in SQL Server 2005. The table will keep a version history of data. There is an ID column of type uniqueidentifier that I set with NEWID() and a timestamp column among others.

When the information is updated on the front end, I will insert a new row using the same ID value, but obviously it will have a more recent timestamp.

Basically, I want my view to display all fields: ID, col1, col2, etc. where ID is unique and pulled from the row with the latest timestamp for that ID.

Can anyone please help me out on this?

Thanks,

Ben

stanl
06-13-2008, 12:10 PM
unless I missed something in your post, don't your want something like

SELECT ID, col1, col2... timestamp ORDER BY timestamp DESC;

Stan

ben.oates
06-16-2008, 02:01 AM
I'm afraid it's not quite that simple. I have multiple lines with the same ID, but when I return the values I only want distinct ID's returned and I want the system to choose the ID with the latest timestamp when there are multiple lines with the same ID. Any suggestions?

stanl
06-17-2008, 05:18 AM
I'm afraid it's not quite that simple. I have multiple lines with the same ID, but when I return the values I only want distinct ID's returned and I want the system to choose the ID with the latest timestamp when there are multiple lines with the same ID. Any suggestions?

Yeah, and I was afraid of that. Were the db normalized it would be a trivial task. Perhaps you can SELECT TOP 1 WHERE (Some SubSelect query), and join the table on itself. You can,of course, always write a procedure/code to obtain what you want. Posting a small subset example would certainly aid.:friends:

ben.oates
06-17-2008, 07:39 AM
It is fairly normalised - if you consider the timestamp and ID to be a composite primary key.

For future reference for anyone that has this problem (or if anyone can suggest a more efficient way for me to do this) the solution I have in SQL Server looks like this:



SELECT ID, col1, col2, coln...
FROM dbo.Users
WHERE ((CAST(ID AS char(36)) + ' & ' + CONVERT(varchar(20), CONVERT(float, CAST(Created AS datetime)))) IN
(SELECT CAST(ID AS char(36)) + ' & ' + CONVERT(varchar(20), CONVERT(float, CAST(MAX(Created) AS datetime))) AS MYID
FROM dbo.Users AS UsersSQ
GROUP BY ID))


Lovely!

For some reason, a conversion of Created to datetime gives differences of milliseconds, so when that is compared as a string there really is no difference! Also, it can't be converted directly into a float, so I have to cast/convert it twice!

stanl
06-18-2008, 03:21 AM
I had a situation with thousands of electrical meters that took readings as a timestamp, and needed to pull the last reading for selected meters. The meter ID's were alphanumeric, and I used CONVERT() to return the timestamp as yyyy:mm:dd:hh:ss which worked, but I wouldn't venture to guess it was any more elegant or faster than your solution.

Stan