Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Get a List of User Views Within a Database
Error Messages
Home > SQL Server Error Messages > Msg 145 - ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
SQL Server Error Messages - Msg 145 - ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Error Message

Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if 
SELECT DISTINCT is specified.

Causes:

As the message describes, this message occurs when you are doing a SELECT DISTINCT combined with the ORDER BY clause and one of the columns in the ORDER BY is not specified as one of the columns in the SELECT DISTINCT.

To illustrate, using the [dbo].[Orders] table in the Northwind database, suppose you want to get a list of unique Customer IDs that had orders ordered by the date of the order with the newest orders first, the following SELECT statement will generate the error above:

SELECT DISTINCT [CustomerID]
FROM [dbo].[Orders]
ORDER BY [OrderDate] DESC
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Solution / Work Around:

To avoid encountering this error, instead of using the DISTINCT clause, you can use the GROUP BY clause to get the unique records from a table.  The query above can be re-written as follows:

SELECT [CustomerID]
FROM [dbo].[Orders]
GROUP BY [CustomerID]
ORDER BY MAX([OrderDate]) DESC

This query will return the unique Customer IDs ordered by the newest orders in descending order.

Related Articles :