'SQL Error when using Order By for SQL database query in PowerBI Desktop

Getting this error when using Get Data with PowerBI Desktop to connect to SQL database

Microsoft SQL: 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.

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'''

I saw I can change the top section to Top XXXXX e.g.,

SELECT Top 1000 PromotionID, PromoDescription, PromoStartDate

How can I return all results without using this hack?

sqlsql-serverdatabasepowerbi


Solution 1:[1]

maybe This can give you your desired results

ORDER BY Priority ASC, PromoStartDate DESC OFFSET 0 ROWS

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bilal Bin Zia

Related Questions