CODEWITHSUNDEEP
sqlsql-server
omixam
omixam

Reputation: 61

SELECT DISTINCT without ordering the rows

I'm doing the following query:

SELECT DISTINCT column1, colum2 FROM table.... WHERE....

but I don't want that the result returned were in order. How can I avoid that sql sort this rows?

Upvotes: 1

Views: 8303

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The select distinct has to identify duplicate rows. There are basically two ways to do this. One is to sort the data (which may be done implicitly using an index). Then duplicate rows appear next to each other. The other is to build a hash table. Duplicate rows appear in the same hash buckets.

For your query, SQL Server is choosing to sort the data. It happens to look like the data is being ordered. On a multi-threaded system, then the data might look sorted, but when you scroll through it, you'll find that the sorting is in chunks (as data is returned from each thread).

Upvotes: 1

Lamak
Lamak

Reputation: 70638

They are not getting sort per se, they are just returning using whatever index is on your table or just scanning the whole table. If you want them sort randomly, then you need to explicitely say so on your query:

SELECT *
FROM (SELECT DISTINCT column1, colum2 
      FROM table.... 
      WHERE....) A
ORDER BY NEWID()

FIDDLE DEMO

Upvotes: 9

Satanicpuppy
Satanicpuppy

Reputation: 1587

ORDER BY isn't required for SELECT DISTINCT (or anything else)...What kind of ordering are you seeing? It may just be that the data was ordered when it was entered into the system.

Upvotes: 1

Related Questions