Order By Clause not working - 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)

Order By Clause not working

Author

Topic

zubairmasoodi
Starting Member

35 Posts

Posted - 2009-03-26 : 08:28:21

Can i please know why Order By is not working in the Procedure given below generating error

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

I have commented the Order By Clause because it didn't worked.

Is there a way out to get the result sorted on CreationDate Desending?

CREATE PROCEDURE dbo.Assessment_GetMyQuestions_sp
(
@UserID INT,
@StartRow INT,
@EndRow INT
)
AS
BEGIN
WITH Set1 AS
(
SELECT Qs.QuestionID,X.QuestionXML,COUNT(TestQuestionID) AS CountInTests,
Qs.CreationDate,row_number() OVER (order by Qs.QuestionID)AS RowNumber FROM
Tbl_Question QS

LEFT JOIN dbo.Tbl_TestQuestions Q ON QS.QuestionID=Q.QuestionID
INNER JOIN dbo.Tbl_QuestionXML X ON Qs.QuestionID=X.QuestionID

WHERE Qs.CreatedByID = @UserID AND ISNULL(Qs.IsActive,0)<>3
GROUP BY Q.QuestionID,Qs.QuestionID,X.QuestionXML,QS.CreationDate
--ORDER By CreationDate DESC
)
SELECT QuestionID,QuestionXML,CountInTests FROM Set1 WHERE RowNumber BETWEEN @StartRow and @EndRow;

END

Thanking in anticipation.

With regards
Zubair Masoodi
(Every day's a school day)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 08:35:19

ORDER BY should not be in the CTE definition.
Put the ORDER BY in the final SELECT statement.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 08:37:40

[code]CREATE PROCEDURE dbo.Assessment_GetMyQuestions_sp
(
@UserID INT,
@StartRow INT,
@EndRow INT
)
AS

WITH Set1
AS (
SELECT Qs.QuestionID,
X.QuestionXML,
COUNT(TestQuestionID) AS CountInTests,
Qs.CreationDate,
row_number() OVER (order by Qs.QuestionID) AS RowNumber
FROM Tbl_Question AS QS
LEFT JOIN dbo.Tbl_TestQuestions as Q ON QS.QuestionID = Q.QuestionID
INNER JOIN dbo.Tbl_QuestionXML as X ON Qs.QuestionID = X.QuestionID
WHERE Qs.CreatedByID = @UserID
AND ISNULL(Qs.IsActive, 0) <> 3
GROUP BY Qs.QuestionID,
X.QuestionXML,
QS.CreationDate
)

SELECT QuestionID,
QuestionXML,
CountInTests
FROM Set1
WHERE RowNumber BETWEEN @StartRow and @EndRow
ORDER BY CreationDate DESC [/code]

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

asgast
Posting Yak Master

149 Posts

Posted - 2009-03-26 : 08:38:09

or use SELECT TOP 100 PERCENT Qs.QuestionID,X.QuestionXML,COUNT(TestQuestionID) AS CountInTests ..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources