如何在Tableau中将指定值设为列首行并升序，及解决SQL导入报错

报错信息 #

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

需求 #

解决上述报错，同时实现将'Market Average'设为name列第一行，其余数据按name升序排列的效果。

原查询代码 #

SELECT * 
FROM (
    SELECT DW_RentHistory.[DW_RentHistoryID] AS id
          ,DW_RentHistory.[propertyID]
          ,bi_property.[name]
          ,DW_RentHistory.[state]
          ,DW_RentHistory.[metro]
          ,DW_RentHistory.[submarket]
          ,DW_RentHistory.[bed]
          ,DW_RentHistory.[bath]
          ,DW_RentHistory.[sqft]
          ,DW_RentHistory.[MONTH] AS month
          ,DW_RentHistory.[YEAR] AS year
          ,DW_RentHistory.[numUnits]
          ,DW_RentHistory.[effectiveRent] AS 'avgEffectiveRent'
          ,DW_RentHistory.[effectiveRentPerSqft] AS 'avgEffectiveRentPerSqft'
          ,DW_RentHistory.[mm_yyyy]
          ,DW_RentHistory.[date]
          ,DATEFROMPARTS(year, month, 1) AS 'Period'
          ,CASE WHEN bi_property.[name] = 'Market Average' THEN 1 ELSE 2 END AS sort_order
      FROM [texas].[dbo].[DW_RentHistory] INNER JOIN bi_property ON
         bi_property.propertyID = DW_RentHistory.propertyID
      WHERE DW_RentHistory.propertyID IN (
        SELECT propertyID FROM listItem WHERE selected = 1 AND listID = 921828
      ) AND DW_RentHistory.Date >= DATEADD(MONTH, -14, GETDATE())
      AND (bi_property.preLeasing = 1 OR bi_property.completed = 1) AND (bi_property.inactive = 0)
    UNION
    SELECT
        0 as id
        ,0 as propert_id
        ,'Market Average' as name
        ,[state]
        ,[metro]
       ,'' as submarket
       ,0 as bed
       ,0 as bath
       ,0 as sqft
       ,[month]
       ,[year]
       ,[numUnits]
       ,[avgEffectiveRent]
       ,[avgEffectiveRentPerSqft]
       ,[date]
       ,[date]
       , DATEFROMPARTS(year, month, 1) AS 'Period'
       ,1 as sort_order
     FROM [dbo].[bi_floorplan_history_metro]
     WHERE metro IN (
        SELECT TOP 1 metro FROM bi_property WHERE propertyid IN (
          SELECT propertyID FROM listItem WHERE selected = 1 AND listID = 921828
        )
     ) AND date >= DATEADD(MONTH, -14, GETDATE())
) AS Subquery
ORDER BY sort_order, [name] ASC;

解决方案 #

方案1：修改SQL，添加TOP 100 PERCENT #

在外层查询中添加TOP 100 PERCENT，让SQL Server允许在当前上下文使用ORDER BY，同时保留原有排序逻辑：

SELECT TOP 100 PERCENT * 
FROM (
    SELECT DW_RentHistory.[DW_RentHistoryID] AS id
          ,DW_RentHistory.[propertyID]
          ,bi_property.[name]
          ,DW_RentHistory.[state]
          ,DW_RentHistory.[metro]
          ,DW_RentHistory.[submarket]
          ,DW_RentHistory.[bed]
          ,DW_RentHistory.[bath]
          ,DW_RentHistory.[sqft]
          ,DW_RentHistory.[MONTH] AS month
          ,DW_RentHistory.[YEAR] AS year
          ,DW_RentHistory.[numUnits]
          ,DW_RentHistory.[effectiveRent] AS 'avgEffectiveRent'
          ,DW_RentHistory.[effectiveRentPerSqft] AS 'avgEffectiveRentPerSqft'
          ,DW_RentHistory.[mm_yyyy]
          ,DW_RentHistory.[date]
          ,DATEFROMPARTS(year, month, 1) AS 'Period'
          ,CASE WHEN bi_property.[name] = 'Market Average' THEN 1 ELSE 2 END AS sort_order
      FROM [texas].[dbo].[DW_RentHistory] INNER JOIN bi_property ON
         bi_property.propertyID = DW_RentHistory.propertyID
      WHERE DW_RentHistory.propertyID IN (
        SELECT propertyID FROM listItem WHERE selected = 1 AND listID = 921828
      ) AND DW_RentHistory.Date >= DATEADD(MONTH, -14, GETDATE())
      AND (bi_property.preLeasing = 1 OR bi_property.completed = 1) AND (bi_property.inactive = 0)
    UNION
    SELECT
        0 as id
        ,0 as propert_id
        ,'Market Average' as name
        ,[state]
        ,[metro]
       ,'' as submarket
       ,0 as bed
       ,0 as bath
       ,0 as sqft
       ,[month]
       ,[year]
       ,[numUnits]
       ,[avgEffectiveRent]
       ,[avgEffectiveRentPerSqft]
       ,[date]
       ,[date]
       , DATEFROMPARTS(year, month, 1) AS 'Period'
       ,1 as sort_order
     FROM [dbo].[bi_floorplan_history_metro]
     WHERE metro IN (
        SELECT TOP 1 metro FROM bi_property WHERE propertyid IN (
          SELECT propertyID FROM listItem WHERE selected = 1 AND listID = 921828
        )
     ) AND date >= DATEADD(MONTH, -14, GETDATE())
) AS Subquery
ORDER BY sort_order, [name] ASC;

方案2：交给Tableau处理排序 #

去掉SQL中的ORDER BY子句，在Tableau数据源中设置排序规则：

1. 导入修改后不带ORDER BY的SQL数据
2. 在Tableau的“数据”面板中，找到sort_order和name字段
3. 右键点击字段，选择“排序”，设置先按sort_order升序，再按name升序

这种方式更符合Tableau的可视化工作流，也避免了SQL层面的语法限制问题。

报错原因 #

Tableau在处理自定义SQL时，会将整个查询作为派生表嵌入到自身生成的SQL语句中，导致原有的ORDER BY处于SQL Server不允许使用的上下文（子查询/派生表）。根据SQL Server规则，这类场景下使用ORDER BY必须配合TOP、OFFSET或FOR XML关键字。

内容的提问来源于stack exchange，提问作者Eduardo Chacon