CODEWITHSUNDEEP
sqlsql-serverfunctionsql-order-by
user9364201
user9364201

Reputation:

How to use Order by in function with SQL Server?

I use order by without function order by work success by use below 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

But when work with function order by not work and error

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.

Use below 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)

Upvotes: 0

Views: 6536

Answers (2)

Max Zolotenko
Max Zolotenko

Reputation: 1132

Replace SELECT to this:

SELECT  top (100) percent

Edited:

learn.microsoft.com

The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless either the TOP or OFFSET and FETCH clauses are also specified. When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

Edited2:

Please, pay attention on this:

The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

The best way to do ordering is:

select * from your_table_func() order by your_column;

Not in function.

Upvotes: 2

Shikhar Arora
Shikhar Arora

Reputation: 886

Inline table valued functions are table expressions. Table expressions cannot have an order by because they are supposed to return a set and there is no meaning of ordering of elements in a set. You should instead add the Sys_Date field in your function result and use order by when calling the function.

SELECT text, 
       value 
FROM   [dbo].[fn_invoice_date](..)
ORDER  BY sys_date

Upvotes: 1

Related Questions