Finding Last Updated with a timestamp

[Astra]

Hi All

I know an SQL Server timestamp seems to be as useful as rocking horse for
show jumping, but I'm hoping you know a 'fudge' to get me round a problem or
at least confirm that it isn't possible.

I have 2 tables, one called ACCOUNTS and one called STOCK.

These tables have the usual ints, varchars, etc and have a timestamp field
as well.

My end user wants to see a simple list of the details in these tables
(individually - no joins present here), but sorted from most recently
updated to never touched.

As the timestamp seems to update each time a transaction hits it I though
this would be perfect, but I've had the following mixed results:

1) I did a simple ORDER BY TIMESTAMP DESC, but because the order is
alphanumeric I don't get a true 'recent to old' list.

2) So I did ORDER BY CAST(TIMESTAMP AS INT) DESC. There is a numeric sort
now, but the timestamp values seem to be hit and miss so that an account
that I know should be near the top is around about the middle.

Do you know how I can achieve the results I want?

Is the timestamp a waste of time?

Thanks

Robbie

[John Bell]

Hi

Timestamp is not a character field.. as stated in books online:

A nonnullable timestamp column is semantically equivalent to a binary(8)
column. A nullable timestamp column is semantically equivalent to a
varbinary(8) column.

When you select the column in QA, it will be displayed as a hexadecimal
number.

If you include DDL ( Create table statements etc... ) and example data (as
Insert statements) along with your query and the output, it may be clearer
what is occuring.

John
"Astra" <in**@NoEmail.com> wrote in message news:40**********@127.0.0.1...

Hi All

I know an SQL Server timestamp seems to be as useful as rocking horse for
show jumping, but I'm hoping you know a 'fudge' to get me round a problem or at least confirm that it isn't possible.

I have 2 tables, one called ACCOUNTS and one called STOCK.

These tables have the usual ints, varchars, etc and have a timestamp field
as well.

My end user wants to see a simple list of the details in these tables
(individually - no joins present here), but sorted from most recently
updated to never touched.

As the timestamp seems to update each time a transaction hits it I though
this would be perfect, but I've had the following mixed results:

1) I did a simple ORDER BY TIMESTAMP DESC, but because the order is
alphanumeric I don't get a true 'recent to old' list.

2) So I did ORDER BY CAST(TIMESTAMP AS INT) DESC. There is a numeric sort now, but the timestamp values seem to be hit and miss so that an account
that I know should be near the top is around about the middle.

Do you know how I can achieve the results I want?

Is the timestamp a waste of time?

Thanks

Robbie

[Erland Sommarskog]

Astra (in**@NoEmail.com) writes:

My end user wants to see a simple list of the details in these tables
(individually - no joins present here), but sorted from most recently
updated to never touched.

As the timestamp seems to update each time a transaction hits it I though
this would be perfect, but I've had the following mixed results:

1) I did a simple ORDER BY TIMESTAMP DESC, but because the order is
alphanumeric I don't get a true 'recent to old' list.

2) So I did ORDER BY CAST(TIMESTAMP AS INT) DESC. There is a numeric
sort now, but the timestamp values seem to be hit and miss so that an
account that I know should be near the top is around about the middle.

Since timestamp is 8 bytes and int only 4, a cast to int could lead to
funny things.

On the other hand, ORDER BY TIMESTAMP DESC will give you the rows in
some order that pertains to updates - and inserts. But it can be a bit
rough. Say that you at some point reloaded the table because of some
maintenance thing, that gave you new timestamp values.

So you are probably better off adding a "moddate" column, which you
update in case of "true" updates, either through stored procedures or a
trigger.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp