SQL Server Error Msg 145 – ORDER BY items must appear in the select list if SELECT DISTINCT is specified

In this blog post, let’s learn about the error message “145 – ORDER BY items must appear in the select list if SELECT DISTINCT is specified.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.

SQL Server Error Message

145 – ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Reason for the Error

SQL Server Error Msg 145 occurs when a SELECT DISTINCT statement is used with an ORDER BY clause and the column being ordered by is not included in the SELECT statement. This issue arises because SQL Server demands that all columns being sorted by be included in the SELECT list when using the DISTINCT keyword.

Here’s an example using the AdventureWorks2019 database to explain SQL Server Error Msg 145:

SELECT DISTINCT ProductID
FROM Production.Product
ORDER BY Name

This statement attempts to extract separate product IDs from the Production.Product table and sort them by the Name field. However, because the Name field is not in the SELECT list, SQL Server raises an error with Msg 145.

Solution

To resolve this issue, we must include the Name column in the SELECT list:

SELECT DISTINCT ProductId, Name
FROM Production.Product
ORDER BY Name

This statement retrieves unique product IDs and names from the Production.Product table that is ordered by the Name column.