title: Order By In a SQL Table Valued Function
body: <p>I've read about this problem on a few different sites, but I still don't understand the solution.
  From what I understand, SQL will optimize the query in the function and sometimes the Order By clause will be ignored.  How can you sort results?</p>
  
  <p>How can I sort results in a simple table valued function like this?</p>
  
  <pre><code>Create function [dbo].fTest
  
  --Input Parameters
  (@competitionID int)
  
  --Returns a table
  RETURNS @table TABLE (CompetitionID int )
  
  as
  
  BEGIN
      Insert Into @table (CompetitionID)
      select CompetitionID from Competition order by CompetitionID desc
      RETURN
  END
  </code></pre>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I found inserting a primary key identity field seems to help (as mentioned in the answer posted Martin Smith).  Is this a good solution?</p>
  
  <pre><code>--Returns a table
  RETURNS @table TABLE
  (
      SortID int IDENTITY(1,1) PRIMARY KEY,
      CompetitionID int 
  )
  </code></pre>
  
  <p>In reference to Martin's answer below, sorting outside of the select statement isn't that easy in my situation.  My posted example is a stripped down version, but my real-life issue involves a more complicated order by case clause for custom sorting.  In addition to that, I'm calling this function in an MVC controller with a LINQ query, which means that custom sorting would have to be added to the LINQ query.  That's beyond my ability at this point.</p>
  
  <p>If adding the identity field is a safe solution, I'm happy to go with that.  It's simple and easy.</p>