Menu
When I run this query, results aren't sorted correctly.
SELECT
SYSDATE - datecolumn a,
SYSDATE - datecolumn sortcolumn
FROM atable
ORDER BY sortcolumn
Produces:
2576.780243055555555555555555555555555556
2586.297013888888888888888888888888888889
2342.294479166666666666666666666666666667
2617.297476851851851851851851851851851852
2624.855104166666666666666666666666666667
2624.855138888888888888888888888888888889
2624.854236111111111111111111111111111111
2372.296643518518518518518518518518518519
2645.257800925925925925925925925925925926
2403.294756944444444444444444444444444444
2676.297696759259259259259259259259259259
When the first line (SYSDATE - datecolumn a,) is removed, everything works fine. Is there any particular reason why this is happening?
938
In this SQL tutorial, we will learn how to use Order by and Group By in SQL. Group By in SQL is used to arrange similar data into groups and Order By in SQL is used to sort the data in ascending or descending order.
Just right-click on one of the column headings in the Data tab, select Columns..., then use the Manage Column dialog's Up/Down arrows to order the columns as desired.
In Oracle, ORDER BY Clause is used to sort or re-arrange the records in the result set. The ORDER BY clause is only used with SELECT statement.
This appears to be a bug. This behaviour looks similar to bug 8675087, which is supposedly fixed in 11.2.0.2 - and indeed I can't reproduce the test case for that. But I still see your behaviour in that version (on SQL Fiddle) and in 11.2.0.3, so this is similar but not exactly the same...
If this is causing you a real issue and you can't find a workaround you're happy with (like swapping the column order; if your sortcolumn is the first expression that uses sysdate it seems to be OK, but swapping columns in your real query might not be convenient) then you should raise a service request with Oracle.
130
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
