CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlstored-procedures
1110
1110

Reputation: 6829

How to use order by clause in inner query

I have a large trouble with order by clause in my inner query.
I use this query to get paged results from database:

;WITH ItemsRank AS
(
select
ROW_NUMBER() OVER(ORDER BY t.CreatedOnDate) AS RowNum, 
t.ItemId
from Tickets t
where t.CompanyId = @CompanyId
group by t.ItemId ...

order by t.CreatedOnDate desc -- this makes me trouble

) ,x as (
select ItemsRank.*, 
c.CommentText ...

WHERE RowNum > @p_From
    AND RowNum <= @p_From + 50
)
select * from x where RowNumComment <= 4


Because query is large I removed all selects, joing, groups etc.
If I put order by at the end of query it works but not as expected.
Because first part of query should get top 50 results in descending order.

This is the error that I get:

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

What am I doing wrong?
Is it possible to fix this query to get ordered items in first part of the query?

Upvotes: 1

Views: 1583

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

Error itself gives you all the info about why it can't be used... :) As @flem said. try this and set as you desire the order. 

ROW_NUMBER() OVER(ORDER BY t.CreatedOnDate desc) AS RowNum,

Or else you could create the CTE without order by and then use a select with order by. Read more on this article.

Upvotes: 1

Paul Fleming
Paul Fleming

Reputation: 24526

As the error indicates, you cannot use ORDER BY in CTEs.

That ORDER BY is not necessary. You specify the order in the OVER clause. You can ORDER BY RowNum in your final query.

Upvotes: 3

Related Questions