expert order by statement

Question

Please assist:

if OBJECT_ID(N'tempdb..#temp') is not null drop table #temp

--select 1 as orderby into #temp

create table #temp
(
orderby int,
id int identity(1,1)
)



insert #temp
(orderby)
select 1

insert #temp
(orderby)
select 2

insert #temp
(orderby)
select 3

declare @phil int,
		@num2 int
		
select @num2 = 2

select orderby
from #temp
order by 
case 
	when 1 = @num2 then (select '1/1/2500')
	--when 1 = @num2 then '1/1/2500'
	else '01/01/1753'
end	desc

select top 1 @phil = orderby
from #temp
order by 
case 
	when 1 = @num2 then (select '1/1/2500')
	--when 1 = @num2 then '1/1/2500'	
	else '01/01/1753'
end	desc




select @phil

Why does the compiler treat select '1/1/2500' different from just '1/1/2500'?

how can I do a select clause and still get the same result?

Thanks,

Phil

Answer 1

Phil, 

case 
when 1 = @num2 then (select '1/1/2500') 
--when 1 = @num2 then '1/1/2500'
else '01/01/1753' 
end

is a constant equal '01/01/1753', variable @num2 is equal to 2.

So top 1 may return any random row.

You need to define order by statement which returns diffrent values for difrent rows.

 

Answer 2

That is weird.

This statement should always return '01/01/1753', because @num2 contains 2. It should be the same as 1=2

case
when 1 = @num2 then (select '1/1/2500')
--when 1 = @num2 then '1/1/2500'
else '01/01/1753'
end

But, in an order by I can reference to the Ordinal Position of the select statement. If so, the statement is the same as orderby = 2

But the result is complete different, if I change the statement to

case
when orderby = @num2 then (select '1/1/2500')
--when orderby = @num2 then '1/1/2500'
else '01/01/1753'
end

the result ist always 2, even which when is uncommend.

I have no idea. 

Answer 3

Futher if you change

order by 
case 
	when 1 = @num2 then (select '1/1/2500')
	--when 1 = @num2 then '1/1/2500'
	else '01/01/1753'
end	desc

 

to

select orderby
from #temp
order by 
case 
	when 1 = @num2 then '1/1/2500'
	--when 1 = @num2 then '1/1/2500'
	else '01/01/1753'
end	desc

 

You'll get different results as well.

Answer 4

What do you mean:

"is a constant equal '01/01/1753', variable @num2 is equal to 2. "

Isn't the value always orderby '01/01/1753' because 1 does not equal 2.

1 01/01/1753
2 01/01/1753
3 01/01/1753

Above is what I expect it's ordering by, I'm just not understanding why the top 1 and the select are different. Shouldn't they always be the same if the order by is the same on both? I've proven here that they aren't consistent.

 

Phil 

 

Answer 5

FYI: There is no guaranteed order on INSERT. Unless you explicitly declare ORDER BY, a select may produce difference results. Microsoft has stated this more times than Obama's false promises.

Adam

---

Ctrl+Z

Answer 6

Yes, but there is an explict ORDER BY.

But the result ist different depending on which WHEN clause in the case statement is uncommend.

Answer 7

Here is how it works. The table contains 3 rows. One row with number 2, one row with number 1 and one row with number 3. I deliberately mention them in this order, because a table is an unordered set of rows.

The statements

 

select @num2 = 2

select orderby
from #temp
order by
case
  when 1 = @num2 then (select '1/1/2500')
  else '01/01/1753'
end  desc

evaluate to

 

select orderby
from #temp
order by '01/01/1753' desc

Since the ORDER BY clause contains no reference to any table data (in other words, it is only a literal), this evalues to

 

select orderby
from #temp

In other words: the entire ORDER BY clause is meaningless. No value of @num2 will ever affect the sorting.

 

---

Gert-Jan

Answer 8

Gert, this is, what Ialso understand.

What I'm not understand, why the result oft the statement is reproducible different depending on the WHEN clause.

Answer 9

That is just by chance.

Or rather, the optimizer does not seem to have to intelligence to figure out that the CASE expression in the ORDER BY clause will always evaluate to just one of two literals. So it doesn't "know" that it can remove the ORDER BY clause. So it still performs a sort. That is what you can see when you inspect the query plan. Aparently in this case, the sort seems to result in "2" as the "top" entry. And since there is no discriminating sorting criterium any order is correct.

 

---

Gert-Jan

Answer 10

I appreciate the feedback on this. Is there anyway to duplicate the result of the top by select in a set-based select when the order by defaults to nothing? In other words, I have code which uses the top by like I've shown and the top by defaults to

select top 1 orderby

from #temp

order by '01/01/1753' desc

Now I need to be able to reproduce getting the first record based on this 'faulty' queryplan like so:

select orderby

from #temp

order by '01/01/1753' desc

So I need the top record to be the same in both cases and I'm wondering how to trick it into doing so, but I can't use a top by in the second select because it's set-based.

Thanks,

Phil

Answer 11

This cannot be done. At least not in a reliable way.

The reason is, because your query is not deterministic. All 3! (=6) orders in which the results can be returned are correct.

In the example, you can add ORDER BY orderby to the query to make it deterministic. In your real query, you could add the primary key to the ORDER BY clause.

 

---

Gert-Jan