title: How to use Order by in function with SQL Server?
body: <p>I use <code>order by</code> without function order by work success by use below code</p>
  
  <pre><code>SELECT 
      Invoice.Text, Invoice.Value 
  FROM
      (SELECT 
           T_Invoice.id,  System_Date As Sys_Date, 
           CONCAT(DATEPART(MM, T_Invoice.System_Date), '/', DATEPART(YY, T_Invoice.System_Date)) AS Text, 
           CONVERT(NVARCHAR, CAST(T_Invoice.System_Date as DATE)) AS Value,
           ROW_NUMBER() OVER (PARTITION BY CONCAT(DATEPART(MM, T_Invoice.System_Date), '/', DATEPART(YY, T_Invoice.System_Date)) ORDER BY T_Invoice.System_Date DESC) AS RowNumber 
       FROM 
           T_Invoice) As Invoice 
  INNER JOIN
      T_Invoice_Items ON Invoice.id = T_Invoice_Items.Invoice
  WHERE 
      T_Invoice_Items.Charge_type = 3 AND Invoice.RowNumber = 1
  ORDER BY 
      Invoice.Sys_Date
  </code></pre>
  
  <p>But when work with function order by not work and error </p>
  
  <blockquote>
    <p>The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.</p>
  </blockquote>
  
  <p>Use below code </p>
  
  <pre><code>ALTER FUNCTION [dbo].[Fn_Invoice_Date]
      (@Charge BIGINT)
  RETURNS TABLE 
  AS
      RETURN 
          (SELECT 
               Invoice.Text, Invoice.Value 
           FROM            
               (SELECT 
                    T_Invoice.id,  System_Date As Sys_Date, 
                    CONCAT(DATEPART(MM, T_Invoice.System_Date), '/', DATEPART(YY, T_Invoice.System_Date)) AS Text, 
                    CONVERT(NVARCHAR, CAST(T_Invoice.System_Date as DATE)) AS Value,
                    ROW_NUMBER() OVER (PARTITION BY CONCAT(DATEPART(MM, T_Invoice.System_Date), '/', DATEPART(YY, T_Invoice.System_Date)) ORDER BY T_Invoice.System_Date DESC) AS RowNumber 
                FROM 
                    T_Invoice) As Invoice 
           INNER JOIN
               T_Invoice_Items ON Invoice.id = T_Invoice_Items.Invoice
           WHERE 
               T_Invoice_Items.Charge_type = @Charge 
               AND Invoice.RowNumber = 1
           ORDER BY
               Invoice.Sys_Date)
  </code></pre>