Skip to Main Content
  • Questions
  • Sorting on columns with many data points having same value

Breadcrumb

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 100+ 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 we 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.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.