ORDER BY items must appear in the select list if SELECT DISTINCT is specified by Roberson Ferreira _

Closed

as By Design Help for as By Design

Type:	Bug
ID:	767076
Opened:	10/11/2012 10:22:35 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

Can not use Distinct with sorting, conversion and alias.

The error occurs:

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

This command works:
"Select Distinct Top 10 CdChamada = Convert(Bigint, CdChamada) From Pessoa Order by CdChamada"

This command, with alias, does not work:
"Select Distinct Top 10 CdChamada = Convert(Bigint, P.CdChamada) From Pessoa P Order by P.CdChamada"

Details (expand)

English

SQL Server 2008 R2 SP1

SQL Engine

Windows 7 Professional

Portuguese (Brazil)

Create Table #Test (MyCode VarChar(10))
Insert Into #Test Values ('100'),('300'),('200')

Select Distinct MyCode = Convert(Bigint, MyCode) From #Test Order by MyCode
go

Select Distinct MyCode = Convert(Bigint, T.MyCode) From #Test T Order by T.MyCode
go

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

MyCode
--------
100
200
300

X64

0 attachments

Posted by Microsoft on 10/15/2012 at 2:06 PM

Hello Roberson,
The behavior you are seeing is by design. It also follows the ANSI SQL specification which is more stricter in lot of cases.

--
Umachandar, SQL Programmability Team

Microsoft