Handling ORDER BY Restrictions in SQL Server Code View Mode

When working with Bold BI code view mode, you may encounter restrictions on using the ORDER BY clause in common table expressions. Bold BI restricts the usage of ORDER BY in SQL Server code view mode as we will be framing this “code view” query as a sub-query when bound to a widget. Using ORDER BY in subquery is restricted in the MS SQL server. This article provides guidance on how to work around these limitations to achieve the desired ordering of results.

Workarounds for ORDER BY Restrictions

To circumvent the ORDER BY restriction, you can use the TOP or OFFSET keywords in your query. Below are examples of how to modify your query using these keywords.

Using TOP

WITH cte_sample AS (
    SELECT TOP 100000 TraceId, EpisodeId, [Percent], Created,
    ROW_NUMBER() OVER (PARTITION BY traceid, episodeid, videoid ORDER BY [percent] DESC) AS RowNum
    FROM VideoStats
)
SELECT ...
FROM cte_sample RR
...
WHERE RowNum = 1;

In this example, the TOP keyword is used to select a set number of rows, and the ORDER BY clause is used within the ROW_NUMBER() function to assign a row number based on the specified order.

Using OFFSET

WITH cte_sample AS (
    SELECT TraceId, EpisodeId, [Percent], Created,
    ROW_NUMBER() OVER (PARTITION BY traceid, episodeid, videoid ORDER BY [percent] DESC) AS RowNum
    OFFSET 100000 ROWS
    FROM VideoStats
)
SELECT ...
FROM cte_sample RR
...
WHERE RowNum = 1;

Dynamic TOP Using Subquery

If you prefer not to hardcode the number of rows to select with TOP, you can use a subquery to dynamically determine the count:

WITH RankedRows AS (
    SELECT TOP (SELECT COUNT(*) FROM VideoWatchedPercentStats) TraceId, EpisodeId, [Percent], Created,
    ROW_NUMBER() OVER (PARTITION BY traceid, episodeid, videoid ORDER BY [percent] DESC) AS RowNum
    FROM VideoWatchedPercentStats
)
SELECT ...
FROM RankedRows RR
...
WHERE RowNum = 1;

Dynamic OFFSET Using Subquery

Similarly, you can use a subquery with OFFSET to dynamically determine the number of rows to skip:

WITH RankedRows AS (
    SELECT TraceId, EpisodeId, [Percent], Created,
    ROW_NUMBER() OVER (PARTITION BY traceid, episodeid, videoid ORDER BY [percent] DESC) AS RowNum
    OFFSET (SELECT COUNT(*) FROM VideoWatchedPercentStats) ROWS
    FROM VideoWatchedPercentStats
)
SELECT ...
FROM RankedRows RR
...
WHERE RowNum = 1;

Conclusion

While SQL Server restricts the use of ORDER BY in certain scenarios, you can use the TOP and OFFSET keywords to achieve the desired ordering in your result sets. These workarounds allow you to specify the order within a subquery or common table expression, ensuring that your data is returned in the correct sequence.

Additional References

• SELECT - ORDER BY Clause (Transact-SQL)
• Using OFFSET and FETCH to Implement Paging in SQL Server
• ROW_NUMBER (Transact-SQL)