Sunday, August 28, 2022

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

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

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.

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

Instead of using the DISTINCT clause, you can use the GROUP BY clause to get the unique records from a table.  

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


Cannot assign a default value to a local variable

Msg 139, Level 15, State 1, Line 1
Cannot assign a default value to a local variable. 

Prior to SQL Server 2008, assigning a default value (or initial value) to a local variable is not allowed; otherwise this error message will be encountered.

This error message can easily be generated using the following DECLARE statement entered in either SQL Server 2000 or SQL Server 2005.
DECLARE @CurrentDate DATETIME = GETDATE()
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.

SQL Server now allows the assigning of a value to a variable in the DECLARE statement.