CODEWITHSUNDEEP
sqlsql-servert-sql
testCoder
testCoder

Reputation: 7385

SQL Server create ordered #Temp table

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?

Upvotes: 1

Views: 2111

Answers (1)

ShaneBlake
ShaneBlake

Reputation: 11096

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...)

Upvotes: 1

Related Questions