Unique retrieval from timestamp

[ben.oates]

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]

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]

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]

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.

[ben.oates]

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]

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