Create a view with ORDER BY clause

Question

I'm trying to create a view with an ORDER BY clause. I have create it successfully on SQL Server 2012 SP1, but when I try to re-create it on SQL Server 2008 R2, I get this error:

Msg 102, Level 15, State 1, Procedure TopUsers, Line 11
Incorrect syntax near 'OFFSET'.

The code to create the view is

CREATE View [dbo].[TopUsersTest] 
as 
select 
u.[DisplayName]  , sum(a.AnswerMark) as Marks
From Users_Questions us inner join [dbo].[Users] u
on u.[UserID] = us.[UserID] 
inner join [dbo].[Answers] a
on a.[AnswerID] = us.[AnswerID]
group by [DisplayName] 
order by Marks desc
OFFSET 0 ROWS

=====================

This is a screen shot of the diagram

I wish to return users' DisplayName and the UserTotalMarks and order this result desc, so the user with the biggest result with be on the top.

Answer 1

Sql server give us the hotfix so we can create view with ORDER BY

here is the link

Micorosft HotFix SQL Server

hope it will help.

Answer 2

I've had success forcing the view to be ordered using

SELECT TOP 9999999 ... ORDER BY something

Unfortunately using SELECT TOP 100 PERCENT does not work due the issue here.

Answer 3

Error is: FROM (SELECT empno,name FROM table1 where location = 'A' ORDER BY emp_no)

And solution is : FROM (SELECT empno,name FROM table1 where location = 'A') ORDER BY emp_no