Does SELECT DISTINCT imply a sort of the results

Question

Does including DISTINCT in a SELECT query imply that the resulting set should be sorted?

I don't think it does, but I'm looking for a an authoritative answer (web link).

I've got a query like this:

Select Distinct foo
From Bar

In oracle, the results are distinct but are not in sorted order. In Jet/MS-Access there seems to be some extra work being done to ensure that the results are sort. I'm assuming that oracle is following the spec in this case and MS Access is going beyond.

Also, is there a way I can give the table a hint that it should be sorting on foo (unless otherwise specified)?

Answer 1

No, it is not implying a sort. In my experience, it sorts by the known index, which may happen to be foo.

Why be subtle? Why not specific Select Distinct foo from Bar Order by foo?

Answer 2

Not to my knowledge, no. The only reason I can think of is that SQL Server would internally sort the data in order to detect and filter out duplicates, and thus return it in a "pre-sorted" manner. But I wouldn't rely on that "side effect" :-)

Answer 3

On at least one server I've used (probably either Oracle or SQL Server, about six years ago), SELECT DISTINCT was rejected if you didn't have an ORDER BY clause. It was accepted on the "other" server (Oracle or SQL Server). Your mileage may vary.

Answer 4

Is it possible that foo is set as the primary key in Oracle but not in Access?

Answer 5

No, the results are not sorted. If you want to give it a 'hint', you can certainly supply an ORDER BY:

select distinct foo from bar order by foo

But keep in mind that you might want to sort on more than just alphabetically. Instead you might want to sort on criteria on other fields. See:

http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx

Answer 6

There is no "authoritative" answer link, since this is something that no SQL server guarantees.

You will often see results in order when using distinct as a side effect of the best methods of finding those results. However, any number of other things can mix up the results, and some server may hand back results in such a way as to not give them sorted even if it had to sort to get the results.

Bottom line: if your server doesn't guarantee something you shouldn't count on it.

Answer 7

From the SQL92 specification:

If DISTINCT is specified, then let TXA be the result of eliminating redundant duplicate values from TX. Otherwise, let TXA be TX.

...

4) If an is not specified, then the ordering of the rows of Q is implementation-dependent.

Ultimately the real answer is that DISTINCT and ORDER BY are two separate parts of the SQL statement; If you don't have an ORDER BY clause, the results by definition will not be specifically ordered.

Answer 8

Yes. Oracle does use a sort do calculate a distinct. You can see that if you look at the explain plan. The fact that it did a sort for that calculation does not in any way imply that the result set will be sorted. If you want the result set sorted, you are required to use the ORDER BY clause.

Answer 9

No. There are a number of circumstances in which a DISTINCT in Oracle does not imply a sort, the most important of which is the hashing algorithm used in 10g+ for both group by and distinct operations.

Always specify ORDER BY if you want an ordered result set, even in 9i and below.

Answer 10

As the answers mostly say, DISTINCT does not mandate a sort - only ORDER BY mandates that. However, one standard way of achieving DISTINCT results is to sort; the other is to hash the values (which tends to lead to semi-random sequencing). Relying on the sort effect of DISTINCT would be foolish.