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.

Transpose

unfortunately OFFSET is only supported on SQL Server 2012 – Suzette

OFFSET is a new keyword in SQL 2012 – Mariel

A view cannot be sorted with an ORDER BY clause. You need to put the ORDER BY clause into any query that references the view. Results of queries are ordered for display in the client application; rows in views and tables are unordered. – Autopsy

What exactly do you expect to accomplish with your order by and offset? – Sole

Topic Updated with details. – Transpose

similar discussion was here [#1623378 – Anabas

Answer 1

I'm not sure what you think this ORDER BY is accomplishing? Even if you do put ORDER BY in the view in a legal way (e.g. by adding a TOP clause), if you just select from the view, e.g. SELECT * FROM dbo.TopUsersTest; without an ORDER BY clause, SQL Server is free to return the rows in the most efficient way, which won't necessarily match the order you expect. This is because ORDER BY is overloaded, in that it tries to serve two purposes: to sort the results and to dictate which rows to include in TOP. In this case, TOP always wins (though depending on the index chosen to scan the data, you might observe that your order is working as expected - but this is just a coincidence).

In order to accomplish what you want, you need to add your ORDER BY clause to the queries that pull data from the view, not to the code of the view itself.

So your view code should just be:

CREATE VIEW [dbo].[TopUsersTest] 
AS 
  SELECT 
    u.[DisplayName], SUM(a.AnswerMark) AS Marks
  FROM
    dbo.Users_Questions AS uq
    INNER JOIN [dbo].[Users] AS u
      ON u.[UserID] = us.[UserID] 
    INNER JOIN [dbo].[Answers] AS a
      ON a.[AnswerID] = uq.[AnswerID]
    GROUP BY u.[DisplayName];

The ORDER BY is meaningless so should not even be included.

---

To illustrate, using AdventureWorks2012, here is an example:

CREATE VIEW dbo.SillyView
AS
  SELECT TOP 100 PERCENT 
    SalesOrderID, OrderDate, CustomerID , AccountNumber, TotalDue
  FROM Sales.SalesOrderHeader
  ORDER BY CustomerID;
GO

SELECT SalesOrderID, OrderDate, CustomerID, AccountNumber, TotalDue
FROM dbo.SillyView;

Results:

SalesOrderID   OrderDate   CustomerID   AccountNumber   TotalDue
------------   ----------  ----------   --------------  ----------
43659          2005-07-01  29825        10-4020-000676  23153.2339
43660          2005-07-01  29672        10-4020-000117  1457.3288
43661          2005-07-01  29734        10-4020-000442  36865.8012
43662          2005-07-01  29994        10-4020-000227  32474.9324
43663          2005-07-01  29565        10-4020-000510  472.3108

And you can see from the execution plan that the TOP and ORDER BY have been absolutely ignored and optimized away by SQL Server:

There is no TOP operator at all, and no sort. SQL Server has optimized them away completely.

Now, if you change the view to say ORDER BY SalesID, you will then just happen to get the ordering that the view states, but only - as mentioned before - by coincidence.

But if you change your outer query to perform the ORDER BY you wanted:

SELECT SalesOrderID, OrderDate, CustomerID, AccountNumber, TotalDue
FROM dbo.SillyView
ORDER BY CustomerID;

You get the results ordered the way you want:

SalesOrderID   OrderDate   CustomerID   AccountNumber   TotalDue
------------   ----------  ----------   --------------  ----------
43793          2005-07-22  11000        10-4030-011000  3756.989
51522          2007-07-22  11000        10-4030-011000  2587.8769
57418          2007-11-04  11000        10-4030-011000  2770.2682
51493          2007-07-20  11001        10-4030-011001  2674.0227
43767          2005-07-18  11001        10-4030-011001  3729.364

And the plan still has optimized away the TOP/ORDER BY in the view, but a sort is added (at no small cost, mind you) to present the results ordered by CustomerID:

So, moral of the story, do not put ORDER BY in views. Put ORDER BY in the queries that reference them. And if the sorting is expensive, you might consider adding/changing an index to support it.

Sole

I understand why Views don't have ORDER BY clauses - but I often write Views as a means of storing a query that I can manually run in SSMS or sqlcmd/osql (things like administrative reports) and I will want the results in some order (usually some date column), but by default they're in some undefined order. It would be nice if I could store a default ORDER BY clause in an Extended Property on the View which SSMS automatically adds when I choose "Script SELECT" in Object Explorer. – Quietus

@Quietus Maybe you should store your query as a stored procedure instead of a view. – Sole

I appreciate all these points but I have users that are working on the system and basically they want views to be naturally ordered which I think is a fair request. I use OFFSET 0 ROWS to achieve this and it works perfectly – Travesty

@Tom Then maybe you should provide them stored procedures instead of views. I suspect OFFSET 0 ROWS will blow up in your face at some point. – Sole

@AaronBertrand That is not handy for doing joins on or day to day checking of small lists. I agree its not ideal and infact one of the big issues with OFFSET is it ensure the full view is always selected regardless of later joins or filters! – Travesty

@Tom Use extreme caution when you value "handy" above all else. – Sole

@AaronBertrand I certainly do not – Travesty

If you have a complicated ordering scheme of some sort, you can add a ROW_NUMBER to the view, and order by that in the final query, or excel, or wherever. – Leveret

@AaronBertrand - I think your comment is the best answer in this thread. – Allantoid

@AaronBertrand But views (and functions) are guaranteed to not have side-effects, sprocs don't. If the view already exists then creating a wrapper-sproc just feels like too much hassle. Ultimately this is a shortcoming of the tooling, not the DBMS (and we should avoid abusing DBMS features to make-up for tooling shortcomings). Not to mention managing permissions on sprocs for users who only have or only need read-only access to a database when using wrapper sprocs. – Quietus

Why would I see the same warnings when putting the ORDER BY on the query that refences a view that does not contain an ORDER BY? – Wert

@Doug Could involve a lot of guesswork. Can you create a db<>fiddle that demonstrates this? – Sole

I will try to do this. But, in a nutshell, it occurs in Power BI using DirectQuery whenever you try to enter a SQL SELECT (not a view) in the Power Query Editor. – Wert

@DougKimzey I saw another question today where someone wanted Power BI to execute a stored procedure, and it seems like the editor might send to SQL Server something like SELECT * FROM ( <your query> ) x - where, unless <your query> has a TOP, ORDER BY is invalid. So if there is a way to specify ordering when you tell Power BI to execute the query, instead of telling Power BI please execute this query that has an ORDER BY in it, that may get you past the error. – Sole

I think that is my post on stackoverflow. What I have found is that: the only way to specify an ORDER BY in Power BI in DirectQuery mode is to include the TOP specifier in the outer query. My concern is that the TOP may be optimized out and that I will not get correct results. Also, DirectQuery does not work with stored procedures. As much as I don't want to bloat my Power BI file, I am giving up on DirectQuery and going to Import mode. I wonder if there is an alternative to ORDER BY using indices . – Wert

If ORDER BY is to be deprecated in some way, how will we obtain ordered results from SQL? – Wert

@Doug they're certainly not deprecating ORDER BY, it's just not valid in certain scenarios (like SELECT FROM (SELECT FROM x ORDER BY y) AS z) because the inner order by (at least without TOP other than 100 PERCENT) is meaningless - it's not the final result and is only used to determine which rows, not the order. If this Power BI tool thing is wrapping any query you write with an outer SELECT FROM (<your query>) x then it should be preventing you from adding an ORDER BY on the inner query (or be smart enough to move it to the outer query which is the only place it makes sense). – Sole

@DougKimzey I don't use Power BI, obviously, can you show me the UI where you're writing this query with an ORDER BY? I can't imagine they don't provide ORDER BY options that would end up on the outer query and allow you to not add an additional ORDER BY at all. – Sole

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.

Annapolis

Thanks! BTW, TOP accepts bigint, so I guess this can be even bigger. So SSMS can waste this order when running select top 1000 (Aaron's answer). however, deleting TOP 1000 from the query is still more convenient than typing in the ORDER BY clause every time. – Wapentake

You gave the user what he wanted but is not a good implementation of a View. One should never use Order By in a View. The offical answer explains well why. – Protection

@NewBie1234: Depends on what the view is for. I just wanted something I could easily right-click --> "Top 1000 rows" in SSMS (or MySQL Workbench). – Annapolis

Answer 3

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.

Travesty

Answer 4

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

Allantoid

Answer 5

As everyone said, ORDER BY is not valid "syntax" inside a VIEW declaration. However, you can trick SQL into accepting a view with an ORDER BY, by only giving it a limited range of rows to report. The real solution of course, is to use a Stored Procedure returning a table. However, to answer the OP, I suggest something like this:

WITH CTE (a,b,c)
AS (SELECT a,b,c FROM Table1)
SELECT TOP n * FROM cte
ORDER BY cte.A

where 'n' is an arbitrary number large enough to report all your data. Don't use TOP 100% as that is fraught.

Sundew

Answer 6

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

Smaragd

Answer 7

Please try the below logic.

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

Cautery

If you want to go this way SELECT TOP 100 PERCENT is simplier – Chronon

Answer 8

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

Starlastarlene

Dislike without any comment :D Wow – Starlastarlene

Those stars at TOP 100 PERCENT incorrect syntax :P – Whitehall

I tried to make the source code TOP 100 PERCENT bold :D – Starlastarlene

Top 100 percent is likely ignored by SQL Server. The order inside the VIEW will be ignored. At least with SQL Server 2016. – Emendation

Answer 9

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

Eberta

Answer 10

use Procedure

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

execute procedure

exec MyView

Whiffletree