distinct and order by - SQL Server Forums

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)

distinct and order by

Author

Topic

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-12-23 : 12:42:42


use northwind
go

select distinct productid, productname
from (select productid, productname from products) d
order by case when 1=1 then productname end

quote:
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

WTF?

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-23 : 12:49:24

The problem is, if you are returning DISTINCT records, the order by has to be part of that list or how can it evaluate the order?

Suppose you have:

FirstName, LastName
----------------------
Jeff, Smith
Bill, Edwards
Jeff, Jones
Jeff, Andrews

and you say:

SELECT DISTINCT FirstName FROM table

you would get:

Jeff
Bill

as results. Now suppose you say:

SELECT DISTINCT FirstName FROM table ORDER BY LastName

What should it return? that is why you must include all of the fields from the ORDER BY if you are using DISTINCT.

Same thing as a GROUP BY.

Even though your CASE statement evaluates to the same thing as a field in your SELECT list, SQL has no way of knowing that.

You just need to put your ORDER BY expression as a field in your SELECT and you are good to go.

- Jeff

Edited by - jsmith8858 on 12/23/2002 12:50:57

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-12-23 : 12:54:02

You know, I did have many many many beers yesterday in preparation for the Raven's loss, but ... isn't productname in the select list?

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-23 : 13:59:38

You are thinking along the lines of GROUP BY's, where you can create your own sort expression of whatever you want, as long as the fields are included in the SELECT.

I was wrong when I said DISTINCT is like a GROUP BY, because there is a difference in the way they work.

Paste the following into your query analzer and look at the execution plans:

----

select distinct contactname + '' from customers order by contactname + ''

select contactname + '' from customers group by contactname order by contactname + ''

-------

The execution plans are different! (though not by much).

Notice that when using DISTINCT, there is only 1 'sort/distinct' step. When using GROUP BY, there are two steps.

This is why in the GROUP BY you can order by an expression that uses the SELECT fields, because first the GROUP is performed, and then the sort expressions are calculated, and the data is sorted.

When using DISTINCT, it is all done in 1 step, because the goal is to remove duplicates and it makes sense to do the sorting then, because dup records will sort next to each other no matter how you decide to sort the records.

It just the way the two different types of SELECTS are implemented. It makes sense that they are executed differently, because by nature GROUP BY needs more overhead than DISTINCT because in the GROUP BY statement you need to accumulate values, calc mins and max's, etc.

So, the ORDER BY is calculated for each record when using DISTINCT, but after the data is GROUPED when using GROUP BY's. Combine that info with the example I gave you, and you can see why all of the fields must be in the SELECT list.

Hopefully, looking at the way SQL executes DISTINCT queries, this makes more sense.

- Jeff

Edited by - jsmith8858 on 12/23/2002 14:03:37

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-12-23 : 14:06:28

quote:
Hopefully, looking at the way SQL executes DISTINCT queries, this makes more sense.

Not yet...there was Jaegermeister involved yesterday, too ...

Explain why this ...


select distinct productid, productname
from (select productid, productname from products) d
order by productname

...runs and this...


select distinct productid, productname
from (select productid, productname from products) d
order by case when 1=1 then productname end

...doesn't.

Jay White
{0}

mfemenel
Professor Frink

1421 Posts

Posted - 2002-12-23 : 16:17:00

That is seriously f'd, and I don't see a whole lot on this error on the web. However, this might help you get around it, not sure what you're working with, but it's a thought.

select d.productid, d.productname
from ( select distinct productid, productname from products
) d
order by case when 1=1 then d.productname end

Mike
"oh, that monkey is going to pay"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-23 : 17:51:44

It's not an error, it's the way the implemented DISTINCT.

Just repeat the CASE expression in the select list or use GROUP BY.

No biggie.

They didn't have to do it this way, that's just the way they chose to implement the DISTINCT operator.

- Jeff

fire1
Starting Member

5 Posts

Posted - 2002-12-26 : 08:13:41

case when 1=1 then productname end

because the: case when.....
is return a result of value and not a field name
so dan not use when have "distinct"

do you understand ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources