SQL Server create ordered #Temp table

Question

I try to create ordered #Temp table:

WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY [Quality] DESC) AS RowNumber, ImageID, Quality, Border, IsBest
  FROM [CatalogImages].[dbo].[Images] ORDER BY Quality)

SELECT *
  INTO #Temp
  FROM Ordered ;

 SELECT * FROM #Temp ;

but I get error:

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

How to create ordered temp table?

Oh, thanks, i was found solution http://stackoverflow.com/questions/985921/sql-error-with-order-by-in-subquery — testCoder, Jul 31 '12 at 20:39
As a side-note: you cannot have an **ordered** table - temporary or not. When you select from a table in SQL, unless you **explicitly specify** an `ORDER BY` - there **is no guaranteed order** — marc_s, Jul 31 '12 at 20:48

score 1 · Answer 1 · answered Jul 31 '12 at 20:41

The Error is because you're trying to do an Order By inside a Common Table Expression, which is not allowed.

Just from what you've posted, it does not appear that your CTE is required. You could just do this :

SELECT ROW_NUMBER() OVER (ORDER BY [Quality] DESC) AS RowNumber, ImageID, Quality, Border, IsBest 
INTO #Temp 
ORDER BY Quality; 

SELECT * 
FROM #Temp 
ORDER BY Quality;

(which doesn't suggest that a temp table is required, either...)

SQL Server create ordered #Temp table

1 Answers1