|
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 :
|
|
|