Sql Server: Common Table Expression (CTE) with ORDER BY clause
- Posted by Sqltimes
- On November 26, 2014
- 0 Comments
Quick one today:
Common Table Expression, since it was introduced in Sql Server 2005 is another great feature. The value is immense and cannot be understated.
- It reduced the need for temporary tables and table variables in scripts a lot.
- Make code more intuitive
- All the tables in multiple CTE’s are touched only when the final query at the bottom of CTE is executed – resulting in better utilization of resources.
- There are lot more uses, but I’ll stop here.
Today, I wanted to touch on one aspect of CTE. Ordering the data inside CTE. Due to the nature of CTE, ordering data inside CTE does not make sense.
Example:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
---- This is not allowed--WITH Sample_CTE (ID)AS ( SELECT Col14 AS ID FROM SomeTable AS C INNER JOIN OtherTable AS S ON C.LocationID = S.LocationID ORDER BY C.PKID ASC)SELECT *FROM Sample_CTEGO |
Error Message:
Msg 1033, Level 15, State 1, Line 6 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.
CTE is just logical representation of a group of qualifying records. The order of records is not guaranteed — as it depends on several factors of the table storage (indexes, heap, order). But if you have TOP clause in the select statement, then it is allowed.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
---- This is allowed--WITH Sample_CTE (ID)AS ( SELECT TOP 1 Col14 AS ID FROM SomeTable AS C INNER JOIN OtherTable AS S ON C.LocationID = S.LocationID ORDER BY C.PKID ASC )SELECT * FROMSample_CTEGO |
Hope this helps,
0 Comments