ORDER BY in CTE

1

Hello

I get an error that I cannot use ORDER BY in a CTE. This is hugely inconvenient, is there any workaround?

I need to sort my table and aggregate Col1 so that older value is first and the newer follow in order.

Thanks!

2

Put the order by on the outer part of the query.

WITH cte
AS (
SELECT ... FROM ... WHERE ...
)
SELECT ... FROM cte WHERE ... ORDER BY ...;
2 Likes
3

I would normally ask if you looked at the documentation:

But that's pointless because I know you didn't, and never do:

The following clauses can't be used in the CTE_query_definition :

  • ORDER BY (except when a TOP clause is specified)
4 Likes
4

Even worse, the error message actually tells you the "TOP" fix.

Msg 1033, Level 15, State 1, Line 4
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.

5

Thanks but it's a bit trickier to be honest!

I need to do:

WITH cte
AS (
SELECT ... FROM ... WHERE ...
)
SELECT ... FROM another_table 
join cte 
WHERE ... 
ORDER BY ...;

Anyway, I suppose what I can do is to sort each table and then join them but I would assume it's a bit of waste?

6

This doesn't make sense - why would you need to sort before joining? If a sort is required for the join then SQL Server will add a sort operator to the execution plan and sort the data.

1 Like
7

if you want to sort each table and join
if it does not let you do that in a CTE then you can do them independently

one idea is hash temporary tables

select name , age into #TempTable1 from Table1 order by age
select dad , age into #TempTable2 from Table2 order by age

select * from #TempTable1 a join #TempTable2 b on a.age = b.age

1 Like
8

Inserting into a table with an order by does not sort the data in the table. The only times using an order by on insert is when the table has an identity and you want to ensure the identity values are generated based on that order - or you are using TOP.

A table doesn't have any order - so therefore neither does a view, a CTE, a derived table or table-valued function (inline or multi-statement).

1 Like
9

Please... post the actual error you're getting. No paraphrasing. Both full, complete, unadulterated error.

2 Likes
10

Yes it would be and makes no sense.

  1. You are not reading docu that experts on this forum are recommending
  2. You just hack and expect it work and are surprised when it doesnot work
  3. You say you have errors but do not share them and expect folks here to be able to use telepathic skills to remotely peer into your computer to figure out the error

Very hard to help you sort things out

2 Likes