1

This question already has an answer here:

My query looks a bit like this:

Select top (@n) *
From MyTable
Where IsNumeric(MyColumn) = 1
Order By Cast(MyColumn as int) desc

Occasionally Sql will produce a query plan that places the Sort operator ahead of the filter operator. This is problematic since the table can contain values which are non-numeric.

Is there a query hint I can apply to force the filter to always come first?

Or would it be better to write my order-by clause to safely handle non-numeric values? I've found two options for this, but I'm not sure which one is better:

Order By Cast(Stuff(MyColumn, 1, Patindex('%[0-9]%', 1), '') as int) desc

Or

Order By Case IsNumeric(MyColumn) When 1 Then Cast(MyColumn as int) else 0 end desc

marked as duplicate by ypercubeᵀᴹ, billinkc, Andriy M, Paul White says GoFundMonica sql-server Nov 8 '16 at 7:20

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 7
    Related question and answer: dba.stackexchange.com/questions/64489/… – ypercubeᵀᴹ Nov 7 '16 at 19:54
  • Your last option ORDER BY CASE is better, but as Aaron Bertrand pointed out in his answer, CASE does not always guarantee left-to-right evaluation. So, the safest method would be two queries. First insert result of SELECT ... WHERE IsNumeric = 1 into a temp table, then select from this temp table your final result. – Vladimir Baranov Nov 8 '16 at 6:58