BUG: ORDER BY and DISTINCT Do Not Eliminate Duplicate Rows

• Microsoft SQL Server version 4.2 for OS/2
• Microsoft SQL Server, version 4.2

       NT:  492 (4.2)

SYMPTOMS

Using a DISTINCT clause with an ORDER BY clause in a SELECT statement, where the column being ordered by is not in the select list, may not cause duplicate rows to be eliminated from the result, for example:

   create table test (name char(30), id int)
   go
   insert test values ("a",1)
   insert test values ("a",2)
   insert test values ("b",2)
   insert test values ("k",3)
   insert test values ("g",4)
   insert test values ("h",5)
   go
   select distinct id from test order by name
   go

   id
   -------
   1
   2
   2
   4
   5
   3

WORKAROUND

To work around the above problem, include the ORDER BY column in the select list in the query.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2 and Microsoft SQL Server version 4.2. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.