| 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 3
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/Workaround:
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 Topics:
|
|
|