SELECT DISTINCT means some kind of result

Question

Does including DISTINCT in a SELECT query mean that the result set should be sorted?

I do not think so, but I am looking for an authoritative answer (web link).

I have a query like this:

Select Distinct foo From Bar 

In oracle, the results are different, but not sorted in order. Jet / MS-Access seems to be doing some extra work to sort the results. I assume that the oracle follows the specification in this case, and MS Access goes beyond.

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

Answer 1

From 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 a is not specified, then the ordering of the strings Q depends on the implementation.

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

Answer 2

Not. There are a number of circumstances where DISTINCT in Oracle does not imply sorting, the most important of which is the hash algorithm used in 10g + for group and individual operations.

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

Answer 3

There is no “authoritative” answer, because this does not guarantee the SQL server.

You will often see results in order when you use excellent as a side effect of the best search methods for these results. However, any number of other things can mix results, and some servers can send results in such a way that they don’t sort, even if they had to be sorted in order to get the results.

Bottom line: if your server does not guarantee something, you should not count on it.

Answer 4

No, this does not mean something like. In my experience, it is sorted by a known index, which may turn out to be foo.

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

Answer 5

As far as I know, no. The only reason I can think of is because SQL Server will internally sort the data in order to detect and filter out duplicates and thus return them in a “pre-sorted” way. But I will not rely on this "side effect" :-)

Answer 6

In my case (SQL server) as an example, I had a list of countries with a numeric value X assigned to each. When I made a selection * from Table order on X, he ordered it by X, but at the same time, countries with a lot of results were also ordered that were not directly implemented. In my experience, I will say that different ones implicitly mean.

Answer 7

On at least one server that I used (probably Oracle or SQL Server, about six years ago), SELECT DISTINCT was rejected unless you had an ORDER BY clause. It was adopted on a "different" server (Oracle or SQL Server). Your mileage may vary.

Answer 8

No, the results are not sorted. If you want to give him a “hint”, you can certainly order ORDER BY:

select excellent foo from bar order from foo

But keep in mind that you can sort more than just alphabetically. Instead, you can sort the criteria by other fields. Cm:

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

Answer 9

As the answers usually say, DISTINCT does not set a kind - only ORDER BY sets this. However, one standard way to achieve DISTINCT results is to sort; the other is to hash values ​​(which, as a rule, lead to semi-random sequencing). Relying on the DISTINCT sorting effect, it would be stupid.

Answer 10

Yes. Oracle really uses sorting to compute a single one. You can see that if you look at the plan of explanation. The fact that he pretended to be for this calculation in no way implies that the result set will be sorted. If you want the result set to be sorted, you need to use the ORDER BY clause.