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

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 767076 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 10/11/2012 10:22:35 AM
Access Restriction Public

Description

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"
Sign in to post a comment.
Posted by Umachandar [MSFT] 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