Why is the Ordered By disregarded?

Question

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 ... ?

order by the column name ENTRYDATE rather than the alias ENTERED? And actually, you are only retrieving matches to TRUNC(sysdate) records, ie no time component. — Glenn, May 21 '12 at 19:35
Why would it accept "order by ENTERED desc", if it did not work correctly? — Gordon Linoff, May 21 '12 at 19:38
@Gordon - it's not a runtime error, it simply doesn't return the results as I want them. — B. Clay Shannon-B. Crow Raven, May 21 '12 at 20:06
@Glenn - I tried replacing ENTERED with ENTRYDATE in the Order By; it makes no difference. — B. Clay Shannon-B. Crow Raven, May 21 '12 at 20:08
Might be best to paste your actual query. If you have ENTRYDATE = TRUNC(sysdate), then you should only be getting ENTRYDATE values that don't have any time component. So there is some kind of mismatch between the query you posted and the results your posted. — Glenn, May 21 '12 at 20:12
@Glenn: I can't post the actual query due to privacy concerns — B. Clay Shannon-B. Crow Raven, May 21 '12 at 20:45
Can you create an example failure that you can post? It's clear to me you're leaving out a vital piece of your query that's causing the problem. Oracle's date ordering works fine. — DCookie, May 21 '12 at 21:30
In your update, it's not obvious that `ENTERED` contains a time component. It looks like the time component is part of the `COMMENTS` field. Is `ENTERED` a `DATE` or a `VARCHAR2`? If `ENTERED` is a `DATE`, are you sure that the date contains the same time component as `COMMENTS`? Can you post output that demonstrates that (i.e. `SELECT to_char(entered, 'yyyy-mm-dd hh24:mi:ss') ...`) — Justin Cave, May 21 '12 at 22:05
In your original update, there is a predicate `ENTRYDATE = TRUNC(sysdate)` that ensures that the only rows that are returned are the rows where the time component of `ENTRYDATE` is midnight. In the query you run for your second update, do the number of rows where the time component is midnight match the number of rows returned by the query from your first update? — Justin Cave, May 21 '12 at 22:24
@Justin: The first query returns 11 rows; the last one returns more than 500 (Toad shows the first 500). — B. Clay Shannon-B. Crow Raven, May 21 '12 at 23:00
I think it's 9g or something like that (I know it's not the latest); how would I know? (I'm not a DB guy, I'm a C# developer). — B. Clay Shannon-B. Crow Raven, May 22 '12 at 00:49
It might pay to add a table alias to all the columns you are referencing. When using ANSI joins before about 10.2.0.5, Oracle wouldn't error if you had the same column name in multiple tables but would return the value from the latest table in the list of tables. Try replacing `entrydate` with `v.entrydate`. — Mike Meyers, May 22 '12 at 06:59
@Mike: Made no difference; the other table involved has no EnryDate column. — B. Clay Shannon-B. Crow Raven, May 22 '12 at 17:04

score 3 · Answer 1 · answered May 21 '12 at 19:41

3

I suspect the problem is that you have a column called "ENTERED" in one of the tables you are joining.

As a consequence, you are ordering by that column, even though it is not in the SELECT list. To fix this, you should do one of the following:

ORDER BY 1

or

ORDER BY ENTRYDATE

(The second having already been mentioned in Glenn's comment.)

answered May 21 '12 at 19:41

Gordon Linoff

1,242,037
58
646
786

"ORDER BY 1 desc" makes no difference – B. Clay Shannon-B. Crow Raven May 21 '12 at 20:09

score 2 · Accepted Answer · answered May 21 '12 at 23:20

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 predicate

entrydate = TRUNC(sysdate)

The query

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

asks for all the rows where entrydate is today at midnight, then sorts on entrydate. By definition, since there is only one possible value for entrydate that satisfies the predicate, that ORDER BY is irrelevant. All 11 rows have exactly the same value for entrydate 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 your ORDER BY clause.

Perhaps you want to order by the time that is stored in the comments column rather than the entrydate. Perhaps you want to change your predicate to return all the rows where entrydate was some time today.

AND trunc(entrydate) = trunc(sysdate)

Perhaps you want to do something else.

when I trunc(entrydate), the query takes "forever" (c. 30 seconds (in Toad)). — B. Clay Shannon-B. Crow Raven, May 21 '12 at 23:55
@ClayShannon - That would make sense either if there is an index on `entrydate` that is being used by the query or simply because the query is now returning orders of magnitude more data. If the problem is that the query is no longer using the index, you could create a function-based index on `trunc(entrydate)`. Before we talk about optimizing a function or figuring out why the results aren't ordering the results the way you expect, is the query returning the correct data? It makes no sense to optimize a query that returns the wrong data. — Justin Cave, May 22 '12 at 00:05
How about: AND entrydate >= trunc(sysdate) AND entrydate < trunc(sysdate)+1 — David Aldridge, May 22 '12 at 07:59

Why is the Ordered By disregarded?

UPDATE

UPDATED AGAIN

2 Answers2