CODEWITHSUNDEEP
sqloracle-databaseselectdistinct
David
David

Reputation: 141

Why does distinct does not give results in the order?

I ordered my results by their id's by:

    CREATE TABLE my_table2 AS SELECT * FROM my_table ORDER BY record_group_id;

now when i execute:

    SELECT DISTINCT record_group_id  FROM my_table2 where rownum <=1000000;

I get gorup id's in random order, though my order by went fine: Here is few of the records in result set

1599890050
1647717203
1647717120
1647717172
1647716972
1647717196
1647717197
1647717205
1599889999
1599889986

What could be the possible reason? Shouldn't DISTINCT statement return records in same order as they are in table?

Upvotes: 0

Views: 289

Answers (5)

Guffa
Guffa

Reputation: 700910

The ordering only determines the order of the source data that is inserted in the table. If there is no clustered index in the table, that means that the records will be stored in that order physically.

However, how the records are stored doesn't guarantee that they will be selected in that order. The execution planner determines the most efficient way to run the query, which means that the data might not be fetched the way that you think it is, and it can differ from time to time as the data changes, or just the statistics about the data.

For a simple query like in the example, you usually get a predictable result, but there is no guarantee, so you always need to sort the query where you fetch the data to be sure to get a predictable result.

One reason that you don't get the data in the order that they are stored in the table in this case, may be that an index is used for filtering the result, and the records are returned in the order of the index rather than the order of the table.

Upvotes: 3

Frank Schmitt
Frank Schmitt

Reputation: 30845

Your assumption that data in the table is ordered is wrong. There is no implicit ordering in a database table - it's just a bag of unsorted data.

If you need ordered data, you'll have to use ORDER BY - there's no way around it (neither DISTINCT nor GROUP BY nor ...), see TomKyte Blog on Order By

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18431

Neither SELECT or DISTINCT defines the order of data.

If you want ordered data explicitly define the Order you need.

SELECT DISTINCT record_group_id 
FROM my_table2 
WHERE rownum <=1000000 
ORDER BY record_group_id;

Upvotes: 4

Bohemian
Bohemian

Reputation: 425458

Using DISTINCT has no effect on order, only on uniqueness of values.

If you want to control order too:

SELECT DISTINCT record_group_id
FROM my_table2
WHERE rownum <= 1000000
ORDER BY record_group_id -- Added this line

Upvotes: 1

JTFRage
JTFRage

Reputation: 397

Use ORDER BY on your SELECT statement:

SELECT DISTINCT record_group_id
FROM my_table2
WHERE rownum <=1000000
ORDER BY record_group_id;

Upvotes: 1

Related Questions