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

by

Troubleshooting Guide: MSSQL Error 145 — ORDER BY / SELECT DISTINCT Conflict

Why This Error Happens

This error occurs because SQL Server’s query engine cannot guarantee deterministic result ordering when a DISTINCT operation removes duplicate rows based on the SELECT list, but the ORDER BY clause references a column that isn’t part of that list. Since duplicate elimination happens before sorting, SQL Server requires every sort key to be visible in the output columns to avoid ambiguous row grouping. This is a T-SQL syntax/logic validation error (Error 145) triggered during query parsing, not a server, permissions, or OS-level fault, though it commonly surfaces after application or reporting-layer updates on Windows Server 2025 / Windows 11 environments running SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your SQL Server 2025 instance.
  • Locate the exact query from the application log, stored procedure, or job step referenced in the Windows Event Viewer (Application log, Source: MSSQLSERVER, Event ID 145).
  • Copy the query into a new query window for isolated testing.

Step 2: Align SELECT and ORDER BY Columns

  • Add every column referenced in ORDER BY directly into the SELECT DISTINCT list.
  • Example fix:
  -- Broken
  SELECT DISTINCT CustomerName FROM Sales ORDER BY OrderDate;

  -- Fixed
  SELECT DISTINCT CustomerName, OrderDate FROM Sales ORDER BY OrderDate;
  • If exposing the extra column is undesirable, wrap the query in a subquery or CTE and select only the needed columns in the outer query without DISTINCT.

Step 3: Refactor Using Window Functions (If Needed)

  • For cases requiring distinct rows sorted by a non-selected column, use ROW_NUMBER() with PARTITION BY instead of DISTINCT.
  • Example:
  SELECT CustomerName FROM (
    SELECT CustomerName, OrderDate,
    ROW_NUMBER() OVER (PARTITION BY CustomerName ORDER BY OrderDate DESC) AS rn
    FROM Sales
  ) t WHERE rn = 1;
  • This avoids the DISTINCT + ORDER BY conflict entirely while preserving sort logic.

Step 4: Update Application/ORM Query Templates

  • If the query originates from an ORM (Entity Framework, Dapper, etc.) or reporting tool (SSRS/Power BI on Windows Server 2025), locate the generated SQL template.
  • Modify the query builder logic to include sort columns in projection lists, or disable auto-generated DISTINCT clauses where unnecessary.

Alternative or Verification Steps

  • Re-run the corrected query in SSMS and confirm it executes without Error 145, then check the Windows Event Viewer to confirm no new Event ID 145 entries appear.
  • As an alternative, use GROUP BY in place of SELECT DISTINCT when you need aggregation-based uniqueness, since GROUP BY allows sorting on non-grouped aggregate expressions more flexibly.