CTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CTE

Author

Topic

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-24 : 01:32:09

Hi

This is my query.. If am run this query i will get error for Invalid column RowID bcoz i generate the rowid in select clause.

what should i do to solve this without using CTE & Temp table...please help on this..

SELECT RANK() OVER (ORDER BY COST DESC) AS ROWID,
COL1,
COL2,
COL3,
COL4,
COL5
FROM TABLE_NAME
WHERE ROWID BETWEEN 1 AND 20
ORDER BY COL1 DESC

-------------------------
R..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-24 : 01:46:48

There is no difference between a CTE or a derived table.
But some tools are not updated to handle CTE.

SELECT		Col1,
		Col2,
		Col3,
		Col4,
		Col5
FROM		(
			SELECT	RANK() OVER (ORDER BY Cost DESC) AS RowID,
				Col1,
				Col2,
				Col3,
				Col4,
				Col5
			FROM	Table_Name
		) AS d
WHERE		RowID BETWEEN 1 AND 20
ORDER BY	Col1 DESC

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources