St. Louis Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Can't use ORDER BY when querying a View?

  • 1.  Can't use ORDER BY when querying a View?

    Bronze Contributor
    Posted Nov 30, 2018 11:52 AM
    Hey guys-
    I'm running into an interesting anomaly and I think I may have found a SQL Server provider bug.  I'm fully aware of not being able to utilize an ORDER BY clause inside of a view, CTE, inline function, etc..  In PowerBI DirectQuery, I'm trying to query the view and add the ORDER BY there, which is fully supported ( I can run the same query from other applications like Excel via Power Query, for example), but Power BI is giving me the SQL Server error that I can't add an ORDER BY inside of a View....WAT?!

    Here's what I'm looking at - Order By is NOT in the View and this query works without issue in Excel, SSMS and some homegrown apps I have at my disposal:


    When I click Ok, I'm presented with a preview of the data as expected.  When I click Load however, I'm presented with this (SQL Server) error message:


    In this scenario, the Order By is important because we're in DirectQuery mode - we can't sort the data using DAX.  Seems silly to persist a table or have to query tables directly because Power BI won't play nice with View rules.  ;-)

    Anyone else seen this one?

    ------------------------------
    Chase Marler
    Database Administrator
    ------------------------------


  • 2.  RE: Can't use ORDER BY when querying a View?

    Silver Contributor
    Posted Nov 30, 2018 12:51 PM
    Found a (small) thread for PBI and some StackOverflow (if you dare) for TOP to possibly get it to work.. not sure on the SQL Server version but some are using PERCENT and others are just putting massively crazy numbers that they know they will never hit. If this doesn't work - maybe we look into a screen share to try some different options.

    SQL Query Error
    Powerbi remove preview
    SQL Query Error
    I have created the following query SQL 2014 and successfully executed it: selectCONVERT(CHAR(7),DATEADD(MONTH,DATEDIFF(MONTH,0,StartDate),0),120) as 'Month & Year',Shift,SUM(DownTime) as 'Month Total Down Time',SUM(DownTime)+COALESCE((select SUM(s2.DownTime)FROM LineData s2wheres2.Shift = s.Shiftand s2.StartDate >= DATEADD(YEAR,DATEDIFF(YEAR,0,s.StartDate),0) --Verify the date is in the same yearand s2.StartDate s2.StartDate --Verify the date s less than s.TDate),0) as 'YTD Total
    View this on Powerbi >


    SQL Error with Order By in Subquery

    ------------------------------
    Alex Powers
    It's Not About The Cell
    Saint Louis MO
    8157182707
    ------------------------------



  • 3.  RE: Can't use ORDER BY when querying a View?

    Bronze Contributor
    Posted Nov 30, 2018 12:58 PM
    The interesting part, is that the article you found is for when you're putting an ORDER BY into the View Definition; which I expect since Views can 't have an ORDER BY in their definition.  However, I should be able to query my view (after its created), and ORDER BY to my heart's content.  I may open a case with Microsoft on this one to see if they can confirm its a bug.  Given that all my other applications can do it, I have a feeling the provider for SQL Server is interpreting my query incorrectly.

    ------------------------------
    Chase Marler
    Database Administrator
    ------------------------------