Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/104906

From BetaArchive Wiki

BUG: ORDER BY and DISTINCT Do Not Eliminate Duplicate Rows

Q104906



The information in this article applies to:


  • Microsoft SQL Server version 4.2x



BUG#  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 


returns


   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.

Additional query words:

Keywords : kbprogramming
Issue type :
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2


Last Reviewed: November 11, 1999
© 2001 Microsoft Corporation. All rights reserved. Terms of Use.