How to prevent sorting if distinct is used? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to prevent sorting if distinct is used?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-25 : 07:40:36

harish writes "i have got a table say EMployee. in That Empname, EmpAddress are the fields(Columns) of the table.

now i want to retrive the Distinct Empname without sorting.
My question is there is there any method to prevent the sorting the resultset?

if we write like "select Distinct(EmpName ) from Employee" the result will be in sorted oredr"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-25 : 07:57:09

SELECT EmpName FROM
(select Distinct(EmpName ) from Employee ) a
ORDER BY NewID()

Damian
Ita erat quando hic adveni.

sahu74
Posting Yak Master

100 Posts

Posted - 2005-05-26 : 12:50:45

I have a similar problem. Using this query ...


SELECT EmpName FROM 
(select Distinct(EmpName ) from Employee ) a
ORDER BY NewID()

...causes the results to be ordered in a different order each time. Is there a way to maintain the order of the names exactly in the order in which they appear in the table?

Any suggestions?

Thank you.
PKS.

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-26 : 13:16:00

You've got your platforms confused...or maybe it's M$ that's confused


USE Northwind
GO

--[CTRL]+K

SELECT DISTINCT EmployeeID FROM ORders

DISTINCT and GROUP BY does not incur a sort as they do in DB2 and Oracle.

You have to add ORDER BY to gaurentee they come out in the correct order.

If they do appear to be sorted, it's a matter of happenstance...

In DB2 oand Oracle the keywords that cause a sort are...drummroll please

DISTINCT
ORDER BY
GROUP BY
UNION

I thought there were 5....I think there's one more....

Brett

8-)

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-05-26 : 13:19:40

quote:
My question is there is there any method to prevent the sorting the resultset?

SQL Server (really any ANSI-compliant database server) won't guarantee the order of a result set UNLESS you explicitly issue an ORDER BY. Therefore you can't guarantee you'll get the results back in the order you put them in. The only way to do that is to add a datetime value that captures when the rows was input and then sort on that. You should be able to do the same thing with an Identity column.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

KLang23
Posting Yak Master

115 Posts

Posted - 2005-05-30 : 06:15:47

quote:
DISTINCT and GROUP BY does not incur a sort as they do in DB2 and Oracle.

You have to add ORDER BY to gaurentee they come out in the correct order.

If they do appear to be sorted, it's a matter of happenstance...

In DB2 oand Oracle the keywords that cause a sort are...drummroll please

DISTINCT
ORDER BY
GROUP BY
UNION

In SQL Server - the commands above absolutely invoke a sort (unless the values are determined unique by the presence of appropriate unique indexes - in some instances) - and that is important to know when optimizing and tuning. "Union All" does not invoke a sort.

The results are usually returned in an order - especially from "Group By" - which requires specific columns to be sorted in a specific column precedence.

The order that data is returned should never be assumed unless there is an "Order By" clause as stated in one of the above posts. If the data is to be returned in the original "stacked" order, some unique identifier (identity) must be assigned to the original data, and the results of the grouped data must be joined back to the original set and ordered by that unique value. In this case, the names should be indexed as well as the unique identifier. Note that the desired results can be obtained using a derived table (inline table) without the use of a #temp table.

I think the original post was less concerned about an internal sort than the order of the data to be returned. Below is a snipped that returns the data as entered - with the exception of duplicates, in which the first instance is returned in "stacked" order, without the use of temporary tables or cursors.


Set NoCount ON
Create table T1 (A_Id int identity primary key not null, A_Name varchar(20) )

Insert into T1 (A_Name) Values ('L')
Insert into T1 (A_Name) Values ('R')
Insert into T1 (A_Name) Values ('W')
Insert into T1 (A_Name) Values ('A')
Insert into T1 (A_Name) Values ('C')
Insert into T1 (A_Name) Values ('L')
Insert into T1 (A_Name) Values ('Z')
Insert into T1 (A_Name) Values ('L')
Insert into T1 (A_Name) Values ('R')
Insert into T1 (A_Name) Values ('K')
Insert into T1 (A_Name) Values ('N')

Create Unique Index T1_X1 on T1 (A_Name, A_Id)

Select T1.A_Name From
  (Select Distinct A_Name From T1) As T2
 Inner Join T1 On T2.A_Name = T1.A_Name
Where T1.A_Id = (Select Min(A_Id) From T1 Where T1.A_Name = T2.A_Name)
Order By A_Id
Drop Table T1

KLang23
Posting Yak Master

115 Posts

Posted - 2005-05-30 : 06:30:26

By the way:

This query will perform better given the same structure:



Select T1.A_Name From T1 
Where T1.A_Id   = (Select Min(A_Id)   From T1 TX   Where T1.A_Name = TX.A_Name)
  and T1.A_Name = (Select Min(A_Name) From T1 TZ   Where T1.A_Id   = TZ.A_Id)
Order By T1.A_Id

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-05-30 : 06:35:30

If we use Damian's routine and alter the function in the ORDER BY to be..


SELECT EmpName FROM 
(select Distinct(EmpName ) from Employee ) a
ORDER BY CHECKSUM(EmpName)

You get "random" rows in a reproduceable order..

DavidM

A front-end is something that tries to violate a back-end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources