[sql-server] Create a view with ORDER BY clause

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.

Please try the below logic.

SELECT TOP(SELECT COUNT(SNO) From MyTable) * FROM bar WITH(NOLOCK) ORDER BY SNO

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.

Just use TOP 100 Percent in the Select:

     CREATE VIEW [schema].[VIEWNAME] (
         [COLUMN1],
         [COLUMN2],
         [COLUMN3],
         [COLUMN4])
     AS 
        SELECT TOP 100 PERCENT 
         alias.[COLUMN1],
         alias.[COLUMN2],
         alias.[COLUMN3],
         alias.[COLUMN4]
        FROM 
           [schema].[TABLENAME] AS alias
          ORDER BY alias.COLUMN1
     GO

From Sql 2012 you can force ordering in views and subqueries with OFFSET

SELECT      C.CustomerID,
            C.CustomerName,
            C.CustomerAge
FROM        dbo.Customer C
ORDER BY    CustomerAge OFFSET 0 ROWS;

Warning: this should only be used on small lists because OFFSET forces the full view to be evaluated even if further joins or filters on the view reduce its size!

There is no good way to force ordering in a view without a side effect really and for good reason.

use Procedure

Create proc MyView as begin SELECT TOP 99999999999999 Column1, Column2 FROM dbo.Table Order by Column1 end

execute procedure

exec MyView

In order to add an ORDER BY to a View Perform the following

CREATE VIEW [dbo].[SQLSTANDARDS_PSHH]
AS


SELECT TOP 99999999999999
Column1,
Column2
FROM
dbo.Table
Order by
Column1

As one of the comments in this posting suggests using stored procedures to return the data... I think that is the best answer. In my case what I did is wrote a View to encapsulate the query logic and joins, then I wrote a Stored Proc to return the data sorted and the proc also includes other enhancement features such as parameters for filtering the data.

Now you have to option to query the view, which allows you to manipulate the data further. Or you have the option to execute the stored proc, which is quicker and more precise output.

STORED PROC Execution to query data

VIEW Definition

USE [DBA]
GO

/****** Object:  View [olap].[vwUsageStatsLogSessionsRollup]    Script Date: 2/19/2019 10:10:06 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--USE DBA
-- select * from olap.UsageStatsLog_GCOP039 where CubeCommand='[ORDER_HISTORY]'
;

ALTER VIEW [olap].[vwUsageStatsLogSessionsRollup] as
(
    SELECT --*
        t1.UsageStatsLogDate
        , COALESCE(CAST(t1.UsageStatsLogDate AS nvarchar(100)), 'TOTAL- DATES:') AS UsageStatsLogDate_Totals
        , t1.ADUserNameDisplayNEW
        , COALESCE(t1.ADUserNameDisplayNEW, 'TOTAL- USERS:') AS ADUserNameDisplay_Totals
        , t1.CubeCommandNEW
        , COALESCE(t1.CubeCommandNEW, 'TOTAL- CUBES:') AS CubeCommand_Totals
        , t1.SessionsCount
        , t1.UsersCount
        , t1.CubesCount
    FROM
    (
        select 
            CAST(olapUSL.UsageStatsLogTime as date) as UsageStatsLogDate
            , olapUSL.ADUserNameDisplayNEW
            , olapUSL.CubeCommandNEW
            , count(*) SessionsCount
            , count(distinct olapUSL.ADUserNameDisplayNEW) UsersCount
            , count(distinct olapUSL.CubeCommandNEW) CubesCount
        from 
            olap.vwUsageStatsLog olapUSL
        where CubeCommandNEW != '[]'
        GROUP BY CUBE(CAST(olapUSL.UsageStatsLogTime as date), olapUSL.ADUserNameDisplayNEW, olapUSL.CubeCommandNEW )
            ----GROUP BY 
            ------GROUP BY GROUPING SETS
            --------GROUP BY ROLLUP
    ) t1

    --ORDER BY
    --  t1.UsageStatsLogDate DESC
    --  , t1.ADUserNameDisplayNEW
    --  , t1.CubeCommandNEW
)
;


GO

STORED PROC Definition

USE [DBA]
GO

/****** Object:  StoredProcedure [olap].[uspUsageStatsLogSessionsRollup]    Script Date: 2/19/2019 9:39:31 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:      BRIAN LOFTON
-- Create date: 2/19/2019
-- Description: This proceedured returns data from a view with sorted results and an optional date range filter.
-- =============================================
ALTER PROCEDURE [olap].[uspUsageStatsLogSessionsRollup]
    -- Add the parameters for the stored procedure here
    @paramStartDate date = NULL,
    @paramEndDate date = NULL,
    @paramDateTotalExcluded as int = 0,
    @paramUserTotalExcluded as int = 0,
    @paramCubeTotalExcluded as int = 0
AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @varStartDate as date 
        = CASE  
            WHEN @paramStartDate IS NULL THEN '1900-01-01' 
            ELSE @paramStartDate 
        END
    DECLARE @varEndDate as date 
        = CASE  
            WHEN @paramEndDate IS NULL THEN '2100-01-01' 
            ELSE @paramStartDate 
        END

    -- Return Data from this statement
    SELECT 
        t1.UsageStatsLogDate_Totals
        , t1.ADUserNameDisplay_Totals
        , t1.CubeCommand_Totals
        , t1.SessionsCount
        , t1.UsersCount
        , t1.CubesCount
        -- Fields with NULL in the totals
            --  , t1.CubeCommandNEW
            --  , t1.ADUserNameDisplayNEW
            --  , t1.UsageStatsLogDate
    FROM 
        olap.vwUsageStatsLogSessionsRollup t1
    WHERE

        (
            --t1.UsageStatsLogDate BETWEEN @varStartDate AND @varEndDate
            t1.UsageStatsLogDate BETWEEN '1900-01-01' AND '2100-01-01'
            OR t1.UsageStatsLogDate IS NULL
        )
        AND
        (
            @paramDateTotalExcluded=0
            OR (@paramDateTotalExcluded=1 AND UsageStatsLogDate_Totals NOT LIKE '%TOTAL-%')
        )
        AND
        (
            @paramDateTotalExcluded=0
            OR (@paramUserTotalExcluded=1 AND ADUserNameDisplay_Totals NOT LIKE '%TOTAL-%')
        )
        AND
        (
            @paramCubeTotalExcluded=0
            OR (@paramCubeTotalExcluded=1 AND CubeCommand_Totals NOT LIKE '%TOTAL-%')
        )
    ORDER BY
            t1.UsageStatsLogDate DESC
            , t1.ADUserNameDisplayNEW
            , t1.CubeCommandNEW

END


GO

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