How to fix Error 5363 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 5363 — “Function May Not Have ORDER BY in OVER or WINDOW Clause”

Why This Error Happens

This error occurs because certain functions, such as aggregate functions used in specific contexts (like %.*ls), do not support an ORDER BY clause within the OVER() or WINDOW clause by T-SQL design restrictions. This is typically a query syntax issue rather than a server configuration problem, often arising when a developer mistakenly applies ranking-function syntax (like ROW_NUMBER() or RANK()) rules to aggregate functions (like SUM() or AVG()) that do not support ordering within that specific clause. It can also surface after migrating queries from an older SQL Server version to SQL Server 2025 if new window function syntax restrictions have changed.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
  • Check the Messages tab in the query results pane to locate the exact line number and function name referenced in the error.
  • Run the following diagnostic query to check recent query history if using Query Store:
  SELECT query_sql_text FROM sys.query_store_query_text WHERE query_sql_text LIKE '%OVER%ORDER BY%';

Step 2: Review the Function Syntax

  • Verify if the function you are using is a Ranking Function (supports ORDER BY) or an Aggregate Function (may not support ORDER BY in specific window contexts).
  • Remove the ORDER BY clause from inside the OVER() parentheses if the function is strictly aggregate-based and doesn’t require frame ordering.
  • Example of an incorrect query:
  SELECT SomeFunction(Column1) OVER (PARTITION BY Column2 ORDER BY Column3) FROM TableName;

Step 3: Modify the Query Logic

  • If ordering is logically required for your calculation, move the ORDER BY outside the window function and apply it to the outer SELECT statement instead.
  • Alternatively, restructure the query using a Common Table Expression (CTE) to pre-sort the data before applying the window function.
  • Save the corrected script to a local path, such as C:\Scripts\SQLFixes\WindowFunctionFix.sql, for version control.

Step 4: Update Database Compatibility Level

  • Sometimes this error appears due to compatibility level mismatches after an upgrade to SQL Server 2025.
  • Check the current compatibility level by running:
  SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
  • If necessary, update it to match the new engine’s requirements:
  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

  • Verification: Execute the corrected query in a test environment to confirm the OVER clause compiles without throwing Error 5363, and check the execution plan to ensure the logic remains accurate.
  • Alternative: If the specific function absolutely requires an order for calculation purposes, consider refactoring the logic to use a subquery with ROW_NUMBER() first, then apply your aggregate function in the outer query layer.