在PowerBI Desktop中对SQL数据库查询使用Order By时发生SQL错误

Question

使用PowerBI Desktop获取数据连接到SQL数据库时出现此错误

Microsoft SQL:ORDER BY子句在视图、内联函数、派生表、子查询和通用表表达式中无效，除非同时指定了TOP、OFFSET或FOR XML。

SELECT PromotionID, PromoDescription, PromoStartDate, 
CASE
WHEN PromoDescription LIKE '%QUINELLA%' OR
PromoDescription LIKE '%EXACTA%' OR
PromoDescription LIKE '%TRIFECTA%' OR
PromoDescription LIKE '%FIRST FOUR%' THEN 1
ELSE 0
END [Priority]
FROM TBS.dbo.Promotion WITH (NOLOCK) 
WHERE ArchivePartition IN (0,1)
AND PromoDescription NOT LIKE '%QUINELLA%' 
AND PromoDescription NOT LIKE '%EXACTA%' 
AND PromoDescription NOT LIKE '%TRIFECTA%' 
AND PromoDescription NOT LIKE '%FIRST FOUR%'
--AND PromoEndDate > getdate()-90
ORDER BY Priority ASC, PromoStartDate DESC'''

我看到我可以将顶部更改为顶部XXXXX，例如

SELECT Top 1000 PromotionID, PromoDescription, PromoStartDate

如何在不使用此破解的情况下返回所有结果？

Answer 1

也许这可以给你想要的结果

ORDER BY Priority ASC, PromoStartDate DESC OFFSET 0 ROWS