ORDER BY in Subquery Restricted - SQL Server

When utilizing Bold BI’s code view mode for SQL Server data source or data sources that use SQL Server as the data store database, users may encounter a limitation regarding the use of the “ORDER BY” clause in SQL Server code view mode.

ORDER BY in Subquery is restricted in SQL server

Using ORDER BY in subquery is restricted in the SQL server. Please find the below screenshot and article for your reference, https://learn.microsoft.com/en-us/answers/questions/1061604/the-order-by-clause-is-invalid-in-views-inline-fun

Bold BI also restricts the usage of ORDER BY in SQL Server code view mode for the same reason as we will be framing this “code view” query as a sub-query when bound to a widget.

Solution

• You can use the TOP keyword after the SELECT or use the OFFSET keyword after the ORDER BY.

Using Top with limited rows:

    WITH RatingList AS (
        SELECT Top 100000 TrackId,EpisodeId,[Percent],Created,ROW_NUMBER() OVER (PARTITION BY TrackId, episodeid, videoid ORDER BY [percent] DESC) AS RowNum
        FROM VideoWatchedPercentStats
    )
    SELECT ChildJourneys.BusinessEntityId, ChildJourneys.Topic AS CompiledJourneyName, SubscriberJourneyTrace.SubscriberId AS EngageRecipientId,
    SubscriberJourneyTrace.EntryMethod,SubscriberJourneyTrace.ChildJourneyId, Entities.Topic AS EntityName,
    Entities.Parent AS BusinessEntityParentId, 'Video Watched' AS EventType,
    RR.Created AS EventDate, [Percent], 0 AS VideoLengthSeconds, 0 AS VideoTimeWatchedSeconds,
    EpisodeId, JourneyEpisodeTemplate_Videos.[Name] AS EpisodeName,SubscriberJourneyTrace.[Language]
    FROM RatingList RR
    LEFT JOIN  JourneyEpisodeTemplate_Videos ON RR.EpisodeId = JourneyEpisodeTemplate_Videos.Id
    INNER JOIN  SubscriberJourneyTrace ON RR.TrackId = SubscriberJourneyTrace.Id
    INNER JOIN ChildJourneys ON SubscriberJourneyTrace.ChildJourneyId = Journeys.Id
    INNER JOIN dbo.Entities ON Journeys.BusinessEntityId = Entities.Id
    WHERE RowNum = 1;

Using OFFSET with limited rows

    WITH RatingList AS (
        SELECT TrackId,EpisodeId,[Percent],Created,ROW_NUMBER() OVER (PARTITION BY TrackId, episodeid, videoid ORDER BY [percent] DESC) AS RowNum OFFSET 100000 ROWS
        FROM VideoWatchedPercentStats
    )
    SELECT Journeys.BusinessEntityId, Journeys.Topic AS CompiledJourneyName, SubscriberJourneyTrace.SubscriberId AS EngageRecipientId,
    SubscriberJourneyTrace.EntryMethod,SubscriberJourneyTrace.ChildJourneyId, Entities.Topic AS BusinessEntityName,
    Entities.Parent AS BusinessEntityParentId, 'Video Watched' AS EventType,
    RR.Created AS EventDate, [Percent], 0 AS VideoLengthSeconds, 0 AS VideoTimeWatchedSeconds,
    EpisodeId, JourneyEpisodeTemplate_Videos.[Name] AS EpisodeName,SubscriberJourneyTrace.[Language]
    FROM RatingList RR
    LEFT JOIN  JourneyEpisodeTemplate_Videos ON RR.EpisodeId = JourneyEpisodeTemplate_Videos.Id
    INNER JOIN  SubscriberJourneyTrace ON RR.TrackId = SubscriberJourneyTrace.Id
    INNER JOIN Journeys ON SubscriberJourneyTrace.ChildJourneyId = Journeys.Id
    INNER JOIN dbo.Entities ON Journeys.BusinessEntityId = Entities.Id
    WHERE RowNum = 1;

If you would rather not specify a number directly in the TOP query and want the entire data, you can use another subquery in-place that will give the total count in your table. Refer below examples

Using Top without Limited Rows:

    WITH RatingList AS (
        SELECT Top(SELECT (COUNT(*)) FROM RatingList RR) TrackId,EpisodeId,[Percent],Created,ROW_NUMBER() OVER (PARTITION BY TrackId, episodeid, videoid ORDER BY [percent] DESC) AS RowNum
        FROM VideoWatchedPercentStats
    )
    SELECT Journeys.BusinessEntityId, Journeys.Topic AS CompiledJourneyName, SubscriberJourneyTrace.SubscriberId AS EngageRecipientId,
    SubscriberJourneyTrace.EntryMethod,SubscriberJourneyTrace.ChildJourneyId, Entities.Topic AS BusinessEntityName,
    Entities.Parent AS BusinessEntityParentId, 'Video Watched' AS EventType,
    RR.Created AS EventDate, [Percent], 0 AS VideoLengthSeconds, 0 AS VideoTimeWatchedSeconds,
    EpisodeId, JourneyEpisodeTemplate_Videos.[Name] AS EpisodeName,SubscriberJourneyTrace.[Language]
    FROM RatingList RR
    LEFT JOIN  JourneyEpisodeTemplate_Videos ON RR.EpisodeId = JourneyEpisodeTemplate_Videos.Id
    INNER JOIN  SubscriberJourneyTrace ON RR.TrackId = SubscriberJourneyTrace.Id
    INNER JOIN Journeys ON SubscriberJourneyTrace.ChildJourneyId = Journeys.Id
    INNER JOIN dbo.Entities ON Journeys.BusinessEntityId = Entities.Id
    WHERE RowNum = 1;

Using OFFSET without Limited Rows:

    WITH RatingList AS (
        SELECT TrackId,EpisodeId,[Percent],Created,ROW_NUMBER() OVER (PARTITION BY TrackId, episodeid, videoid ORDER BY [percent] DESC) AS RowNum OFFSET (SELECT (COUNT(*)) FROM RatingList RR) ROWS
        FROM VideoWatchedPercentStats
    )
    SELECT Journeys.BusinessEntityId, Journeys.Topic AS CompiledJourneyName, SubscriberJourneyTrace.SubscriberId AS EngageRecipientId,
    SubscriberJourneyTrace.EntryMethod,SubscriberJourneyTrace.ChildJourneyId, Entities.Topic AS BusinessEntityName,
    Entities.Parent AS BusinessEntityParentId, 'Video Watched' AS EventType,
    RR.Created AS EventDate, [Percent], 0 AS VideoLengthSeconds, 0 AS VideoTimeWatchedSeconds,
    EpisodeId, EpisodeTemplate.[Name] AS EpisodeName,SubscriberJourneyTrace.[Language]
    FROM RatingList RR
    LEFT JOIN  EpisodeTemplate ON RR.EpisodeId = EpisodeTemplate.Id
    INNER JOIN  SubscriberJourneyTrace ON RR.TrackId = SubscriberJourneyTrace.Id
    INNER JOIN Journeys ON SubscriberJourneyTrace.ChildJourneyId = Journeys.Id
    INNER JOIN dbo.Entities ON Journeys.BusinessEntityId = Entities.Id
    WHERE RowNum = 1;