I want my SQL to return all records for a given date, with the most recent one on top (the column I'm ordering by contains both date and time - contains entries such as "5/21/2012 11:48:04 AM").
I would think my sql (below) would do this. However, the actual results disregard the time element. They are returned like this:
5/21/2012 10:48:04 AM 5/21/2012 10:12:04 AM 5/21/2012 9:48:04 AM 5/21/2012 10:54:04 AM 5/21/2012 11:48:04 AM ...
(IOW, the results returned are just randomly ordered, as far as the time element goes)
the query is:
SELECT ENTRYDATE ENTERED, ENTEREDBYABC ABC
FROM
SomeTable v
LEFT JOIN SomeTable w ON v.someCol = w.someCorrespondingCol
WHERE
ABC = :abc AND ENTRYDATE = trunc(sysdate)
ORDER BY ENTERED DESC
UPDATE
More specific query and results:
This (column and table names have been changed):
SELECT ENTRYDATE ENTERED, ENTEREDBYABCID ABCID, COMMENTS
FROM
WHITMAN.HOLLOWSKY@ATTORNEY v
LEFT JOIN ABCworker w ON v.enteredbyabcid = w.abcid
WHERE
ABCID = 124393 AND ENTRYDATE = TRUNC(sysdate)
ORDER BY ENTRYDATE desc
...returns records with:
ENTERED ABCID COMMENTS
5/21/2012 1234 At 1:36 PM, ...
5/21/2012 1234 At 1:36 PM, ...
5/21/2012 1234 At 9:23 AM, ...
5/21/2012 1234 At 11:07 AM, ...
5/21/2012 1234 At 11:12 AM, ...
5/21/2012 1234 At 1:42 PM, ...
5/21/2012 1234 At 11:02 AM, ...
5/21/2012 1234 At 9:19 AM, ...
. . .
UPDATED AGAIN
With the query:
select entrydate from WHITMAN.HOLLOWSKY@ATTORNEY order by entrydate desc
I get:
5/21/2012 3:15:50 PM
5/21/2012 3:15:35 PM
5/21/2012 3:15:25 PM
5/21/2012 3:15:25 PM
5/21/2012 3:14:31 PM
5/21/2012 3:14:22 PM
5/21/2012 3:14:11 PM
. . .
IOW, it works just fine.
Whether "entrydate" is a DateTime column - I reckon so, but I don't have privileges to look at the table structure, so ... ?
Your
ORDER BY
is being honored. The problem is that the time component on all 11 rows that you are selecting must be midnight in order to satisfy the predicateThe query
asks for all the rows where
entrydate
is today at midnight, then sorts onentrydate
. By definition, since there is only one possible value forentrydate
that satisfies the predicate, thatORDER BY
is irrelevant. All 11 rows have exactly the same value forentrydate
so sorting on that value produces an arbitrarily ordered result. Oracle could, quite correctly, return the 11 rows in any order and still be honoring yourORDER BY
clause.Perhaps you want to order by the time that is stored in the
comments
column rather than theentrydate
. Perhaps you want to change your predicate to return all the rows whereentrydate
was some time today.Perhaps you want to do something else.