The Order by Clause Is Invalid in Views, Inline Functions, Derived Tables, Subqueries, and Common Table Expressions

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions

You do not need to use ORDER BY in inner query after WHERE clause because you have already used it in ROW_NUMBER() OVER (ORDER BY VRDATE DESC).

SELECT 
    * 
FROM (
    SELECT 
        Stockmain.VRNOA, 
        item.description as item_description, 
        party.name as party_name, 
        stockmain.vrdate, 
        stockdetail.qty, 
        stockdetail.rate, 
        stockdetail.amount, 
        ROW_NUMBER() OVER (ORDER BY VRDATE DESC) AS RowNum  --< ORDER BY
    FROM StockMain 
    INNER JOIN StockDetail 
        ON StockMain.stid = StockDetail.stid 
    INNER JOIN party 
        ON party.party_id = stockmain.party_id 
    INNER JOIN item 
        ON item.item_id = stockdetail.item_id 
    WHERE stockmain.etype='purchase' 
) AS MyDerivedTable
WHERE 
    MyDerivedTable.RowNum BETWEEN 1 and 5 

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML

In almost all cases, you can simply drop the CTE's ORDER BY clause. Even if it were permitted syntactically (it is in other RDBMS), it has no effect on your query's result the way you wrote it.

Now, if for some reason, you absolutely have to keep it there, you can add a TOP clause, e.g. one without any effect such as TOP 100 PERCENT, i.e.:

with requests as    (
  select top 100 percent
    IRF.Id as Id,
    ...

A full, simplified example:

-- Doesn't work
with t (a) as (
  select a
  from (values (1),(3),(2)) t (a)
  order by a
)
select a from t order by a

-- Works
with t (a) as (
  select top 100 percent a
  from (values (1),(3),(2)) t (a)
  order by a
)
select a from t order by a

sql server The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions

Just use the below code

    SELECT DISTINCT TOP 28 [DATECOL]
        ,[CODE ARTICLE]
        ,[CODE SITE]
        ,[CODE STRUCTURE]
    FROM [VENTE] T2
    WHERE PROMO = 0
        AND ([CODE ARTICLE] IS NOT NULL)
        AND ([CODE STRUCTURE] IS NOT NULL)
        AND ([PRIX DE REVIENT] IS NOT NULL)
        AND [CODE ARTICLE] = '    714180291'
        AND [CODE SITE] = '    7'
    ORDER BY [DATECOL]

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries,...., unless TOP or FOR XML is also specified

You can use subquery :

select col1, count(*) something1,
       count(*)-1 / (select count(distinct col3) from dbname where col1 = d.col1) something2
from dbname d
group by col1;

However, you can also directly warp it into single SELECT statement

select col1, count(*) something1,
       (count(*)-1 / count(distinct col3)) something2
from dbname d
group by col1;

Edit: Be aware with divide by zero error, so you can include case expression

select col1, count(*) something1,
       coalesce((count(*)-1/(case when count(distinct col3) = 0 
                                  then null else count(distinct col3) end)), 0) something2
from dbname d
group by col1;

Can't use ORDER BY in a derived table

You don't need a subquery for this:

SELECT TOP 20 *
FROM TBArticles
ORDER BY id_art DESC

The documentation is quite clear on the use of ORDER BY in subqueries:

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.

OrderBy clause not working on Microsoft SQL?

MSSQL definitely supports order by but in your case as the error already suggested

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions

You cannot use order by in subqueries like you have done.
Go through the mssql order by documentation for more understanding

And by looking at your query I think its unnecessary to add any order by as it is doing nothing. You can remove the clause.

SELECT count(*) AS AGGREGATE
FROM [items]
INNER JOIN
  (SELECT [model_name],
          MAX(VERSION) AS max_version
   FROM [items]
   WHERE [items].[deleted_at] IS NULL
   GROUP BY [model_name]
) AS [max_version_table] ON [items].[model_name] = [max_version_table].[model_name]
AND [items].[version] = [max_version_table].[max_version]
WHERE [items].[deleted_at] IS NULL

---

Related Topics