I understand that using :
Select * from MyTable Order By MyTimeStampField DESC
will not produce a result set ordered by MyTimeStampField with the most recent "time stamped" record being first and the oldest being the last.

I have tried numerous efforts with Cast and Interval with no success.

My latest is:
Select * from MyTable Order By CAST(CURRENT_TIMESTAMP() - MyTimeStampField AS INTERVAL MSECOND)

... again with no success.

As I just wanted a result set starting with the most recent timestamp in the table, this shouldn't be so difficult.

What am I missing here?

On 4/11/2013 7:49 PM, Norman Clark wrote:
> I understand that using :
> Select * from MyTable Order By MyTimeStampField DESC
> will not produce a result set ordered by MyTimeStampField with the most recent "time stamped" record being first and the oldest being the last.

This should work just fine as long as timestamp fields are properly
populated and unique.

How is your timestamp field filled in and what is not working ?

Timestamp is based on time+date down to ms so for batch inserts or such
you can't guarantee uniqueness for every row but the sort would be "off"
onl y in thkose batched sets which have identical timestamp (overall
order of records should be be fine).

Raul

Norman


I'm with Raul - it works fine - just tested here on one of my table and the results are what I would expect.

Can you post the table definition from EDBManager and a screen shot of the "wrong" sequence.

Also which version of ElevateDB are you running?

Roy Lambert [Team Elevate]

Norman Clark wrote:
............
Select * from MyTable Order By CAST(CURRENT_TIMESTAMP() - MyTimeStampField AS INTERVAL MSECOND)

to produce a result set starting with the most recent timestamp in the table,
.............

Thank you all.  I just wanted to confirm that indeed the above DOES WORK as expected.  It was late in the day and I had spent far too much time chasing CASTS and INTERVALs.  I subsequently found my issue in a synch procedure that altered the result set.  My Bad.

Tim Y: perhaps we should have a separate forum for us to post dumb questions where we do not deserve a reply

Thanks again all.
... Norm

Norman,

On 4/12/2013 10:03 AM, Norman Clark wrote:
> Thank you all.  I just wanted to confirm that indeed the above DOES WORK as expected.  It was late in the day and I had spent far too much time chasing CASTS and INTERVALs.  I subsequently found my issue in a synch procedure that altered the result set.  My Bad.

Great.  What I also meant was that the "Select * from MyTable Order By
MyTimeStampField DESC" works fine for me - no need to cast and compute
ms. Resultset is sorted with most recent timestamp on top and then rest
properly ordered.

Raul

Norman

>Tim Y: perhaps we should have a separate forum for us to post dumb questions where we do not deserve a reply

I think it would either be empty or we'd all migrate there

I know what you mean but my take is all questions deserve an answer. Sometimes the very act of posting a "dumb" question has given me the answer as I've been typing, sometimes an hour or so after posting.

Roy