Select Distinct without sorting

Question

I used a Select Distinct query, which resulted me a sorted data. Is there anyway that i dont get data sorted?

Answer 1

I'll try to elaborate a bit as to what's going on and why... though I agree with @vic's comment to the question...

• Without explicitly stating an order (via an order by clause) there is absolutely no guarantee of any order in the result set.
• Practically speaking, many queries will return a consistent order based on the query plan and how the data is actually stored and accessed... DO NOT RELY ON THIS!
• Specifically, for a distinct query, the sql engine will sort the data so that it can be sure to remove any duplicates.

In short, if the order of the result set matters (even if the desired order is "random") you must ALWAYS explicitly state it. That said, from a purely set-based-math/sql standpoint, the order of the result shouldn't matter.

Answer 2

Put this at the end of your query. This will effectively randomize the results which then will appear to you non-sorted ;)

ORDER BY Rnd([ID]);

Replace the ID with primary key of the table. In Access SQL it is possible to call certain VB Functions directly. In this case the Rnd function can be called in a query and fed a seed value from the data being sorted.