# expert order by statement

• ### Question

```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

Wednesday, July 13, 2011 11:54 PM

• 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
• Proposed as answer by Thursday, July 14, 2011 9:54 PM
• Marked as answer by Thursday, July 14, 2011 11:18 PM
Thursday, July 14, 2011 9:52 PM

### All replies

• 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.

• Proposed as answer by Thursday, July 14, 2011 7:56 PM
Thursday, July 14, 2011 1:25 AM
• 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.

Thursday, July 14, 2011 7:24 AM
• 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.
Thursday, July 14, 2011 3:55 PM
• 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

Thursday, July 14, 2011 4:03 PM
• 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.

Ctrl+Z
Thursday, July 14, 2011 4:06 PM
• Yes, but there is an explict ORDER BY.

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

Thursday, July 14, 2011 7:59 PM
• 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
• Proposed as answer by Thursday, July 14, 2011 8:38 PM
Thursday, July 14, 2011 8:01 PM
• 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.

Thursday, July 14, 2011 8:17 PM
• 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
Thursday, July 14, 2011 8:33 PM
• 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

Thursday, July 14, 2011 9:20 PM
• 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
• Proposed as answer by Thursday, July 14, 2011 9:54 PM
• Marked as answer by Thursday, July 14, 2011 11:18 PM
Thursday, July 14, 2011 9:52 PM