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 11Incorrect   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Please try the below logic   SELECT TOP SELECT COUNT SNO  From MyTable    FROM bar WITH NOLOCK  ORDER BY SNO

User · Answer

use Procedure  Create proc MyView as  begin   SELECT TOP 99999999999999   Column1    Column2   FROM   dbo Table   Order by   Column1 end  execute procedure  exec MyView

User · Answer

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

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

Examples related to sql-server

Examples related to database

Examples related to tsql

Examples related to view

Examples related to sql-server-2008-r2