Order by Date in Oracle

Question

Trying to order by as follows:

SELECT DISTINCT TO_DATE(e.PUB_DATE_PRINT, 'DD Mon YYYY') AS "Publication Date"
FROM TABLE
ORDER BY e.PUB_DATE_PRINT DESC

I'm getting an error here because of the date, as there are some nulls in my e.PUB_DATE_PRINT column.

• If I change to TO_CHAR, I get duplicates (there are some joins in my full query)
• If I SELECT DISTINCT and ORDER BY TO_CHAR(e.PUB_DATE_PRINT, 'DD Mon YYYY'), I get the correct number of records, but I can't order it correctly, as it orders by the 'DD'

I can't seem to work this out...! Any help would be greatly appreciated.

Answer 1

Order by is the last clause in the query to be applied, so it already has the final resultset to work on.

Is this what you want?

SELECT DISTINCT TO_DATE(e.PUB_DATE_PRINT, 'DD Mon YYYY') AS "Publication Date"
FROM TABLE
ORDER BY 1