Skip to Main Content
  • Questions

  • Sorting on columns with many data points having same value

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, Padmanabha.

Asked: April 07, 2021 - 11:18 am UTC

Last updated: April 13, 2021 - 1:49 pm UTC

Version: Oracle 19c standard edition 2 release 19.0.0.0.0 -Productionversion 19.10.0.0.0

Viewed 1000+ times

You Asked

We are trying to confirm the expected behavior of Oracle for how it handles sorting on a column where many of the data points are the same value. For example if I queried the following table and sorted on the Data column I might get changing result order on each query like the two iterations below.

(First query)

|  ID |  Data |
-----------------
| 1   | James |
| 2   | James |
| 3   | James |


(Second query) 

|  ID |  Data |
-----------------
| 3   | James |
| 1   | James |
| 2   | James |


We believe it is expected that this can happen and that Oracle does not guarantee the order of row for this type of query. Regardless,we know we can update the application to add a second sort on a unique column, but that is a huge development effort. So wanted to know if perhaps there is something we can do on the database side to avoid making large application changes.?

and Chris said...

Correct, when sorting on non-unique values the database can return rows with the same value in any order.

The only way to guarantee that you'll get the rows in the order you want is to add a unique column to the sort.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.