CODEWITHSUNDEEP
sql-server
rgx71
rgx71

Reputation: 857

Using distinct clause in SQL Server

In SQL Server 2008, using distinct clause is always doing an implicit order by or I need to specify an order by for that? I want to be sure that using distinct put data in order.

Here you have an example, distinct is doing order by

create table #MyTable (id int)

insert into #MyTable values (3)
insert into #MyTable values (2)
insert into #MyTable values (8)

select distinct id from #MyTable

Upvotes: 1

Views: 107

Answers (2)

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391276

Although the typical implementation of distinct is done using some kind of ordered data structure, the order it uses may not be the one you need.

There are:

  • No guarantees that the data will be ordered any which way
  • No guarantees that the same query on the same data later/tomorrow will return the data in the same (arbitrary) order
  • No guarantees that the observed ordering will be consistent

The distinct clause does not imply ordering. As such, if you need the data ordered in a particular manner, you have to add an order by clause to the query.

Also note that one of the data structures that can be used is a hashtable/hashset, and though these may produce data that looks ordered if there are only a few values placed into them, with larger quantities this will break down, and regardless, this is implementation specific and undocumented. Do not rely on any such behavior.

Upvotes: 5

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

DISTINCT clause has nothing to do with ordering records. You have to explicitly use ORDER BY clause for sorting.

select distinct id 
from #MyTable
Order By id

Upvotes: 1

Related Questions