locked
expert order by statement RRS feed

  • 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


    Wednesday, July 13, 2011 11:54 PM

Answers

  • 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 Naomi N Thursday, July 14, 2011 9:54 PM
    • Marked as answer by philliptackett77 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 Gert-Jan Strik 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.

    Adam


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