Member Avatar for PhilEaton

I need to order these dates in descending order (last should be first) and in fact how they now appear. However they do not necessarily appear like those so I need a SQL Order By clause to order these descending:

2012-01-23 09:53:24.097105

2012-01-20 17:31:57.565458

2012-01-20 17:01:09.154587

2012-01-20 16:28:40.685735

2012-01-16 12:03:54.99954

Here is my SQL (which returns an error):

SELECT
    TransmissionSend.CreateTimestamp
FROM
    Interface_SFAX.TransmissionSendItem
ORDER BY TransmissionSend.CreateTimestamp DESC

I need help creating a working SQL query that displays the times in descending order... Any help would be great!

Thanks!!

Edited by PhilEaton
  • 2 Contributors
  • 3 Replies
  • 535 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by JorgeM

Dani AI

Generated

Short version: the ORDER BY was referring to the wrong table name — spotted it. Use a consistent table name or alias for both the SELECT and ORDER BY. For example:

SELECT tsi.CreateTimestamp
FROM Interface_SFAX.TransmissionSendItem AS tsi
ORDER BY tsi.CreateTimestamp DESC;

If CreateTimestamp is a proper DATETIME/TIMESTAMP column (including fractional seconds), the query above will return newest rows first. If the column is stored as text, ISO timestamps normally sort correctly as strings, but variations or trailing microseconds can break things. Convert to a datetime for safe ordering:

ORDER BY STR_TO_DATE(tsi.CreateTimestamp, '%Y-%m-%d %H:%i:%s.%f') DESC

If your MySQL build does not support fractional-second parsing, strip the fraction before converting:

ORDER BY STR_TO_DATE(LEFT(tsi.CreateTimestamp,19), '%Y-%m-%d %H:%i:%s') DESC

Quick troubleshooting checklist:

  • Verify the table/alias and column names match exactly (this is the original issue that caused the error).
  • Check the column type with DESCRIBE Interface_SFAX.TransmissionSendItem; or SHOW CREATE TABLE ....
  • Test ordering on a small sample with LIMIT 10 to confirm results.
  • Prefer storing timestamps in proper DATETIME/TIMESTAMP columns so ORDER BY works efficiently and reliably.

This fixes the problem reported by and avoids subtle ordering bugs caused by type or naming mismatches.

Member Avatar for JorgeM

Your SQL statement above shows you selecting from a different table than your ORDER BY field.

TransmissionSend vs TransmissionSendItem.

Member Avatar for PhilEaton

Right :) It was a pretty stupid mistake.

Member Avatar for JorgeM

Not a problem, sometimes a fresh pair of eyes is helpfull.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.