SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 03:38 AM
| ||||
| ||||
 Finding Last Updated with a timestamp 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      |
|
02-29-2008, 03:38 AM
| |||
| |||
| Re: Finding Last Updated with a timestamp 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" <info@NoEmail.com> wrote in message news:40f7b5ea$1_4@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 > > > |
|
02-29-2008, 03:39 AM
| ||||
| ||||
| Re: Finding Last Updated with a timestamp Astra (info@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, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp |
Linear Mode
