Try using an ordinal reference in the ORDER BY
As in ORDER BY 1, 2, 3

_______________________

Wendy Neu
Seapine Software, Inc.
513.754.1655, x1547
_______________________
-----Original Message-----
From: Todd Melcher [mailto:mssqldba-ezmlmshield-x10182540.[Email address protected]
Sent: Friday, December 28, 2007 11:11 AM
To: LazyDBA Discussion
Subject: Error on Select statement

When I run the following select statement against one database it runs
fine. When I run it against another database that is almost identical
in structure, I get the following error. The new DB is normalized.



SELECT DISTINCT CONVERT(VARCHAR(20), VersionsProLink.ReleaseDate, 102)
AS ReleaseDate,

FacilityZipCodesProLink.versionid AS VersionID,

DiscountLevelTypesProLink.Description AS
DiscountLevelType,

COUNT(*) AS TotalRecords

FROM DEVSRV.ProLinkCorporate.dbo.FacilityZipCodes AS
FacilityZipCodesProLink,

DEVSRV.ProLinkCorporate.dbo.DiscountLevelTypes AS
DiscountLevelTypesProLink,

DEVSRV.ProLinkCorporate.dbo.Versions AS VersionsProLink

WHERE FacilityZipCodesProLink.discountleveltypeid =
DiscountLevelTypesProLink.discountleveltypeid AND

FacilityZipCodesProLink.VersionID =
VersionsProLink.VersionID

GROUP BY FacilityZipCodesProLink.VersionID,

VersionsProLink.ReleaseDate,

DiscountLevelTypesProLink.description

ORDER BY VersionsProLink.ReleaseDate DESC,

FacilityZipCodesProLink.versionid DESC,

DiscountLevelTypesProLink.description DESC





Error:



Msg 145, Level 15, State 1, Line 1

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



I don't understand why this is happening. Both Databases are on the
same instance of SQL Server 2005



Todd Melcher

Database Administrator

Pitney Bowes PSI Presort Services








This email message may contain confidential, proprietary and/or privileged information. It is intended for use of the intended recipient(s). If you have received it in error, please immediately advise the sender by reply email and then delete the message. Any disclosure, copying, distribution or use of the information contained in this email message to or by anyone other than the intended recipient is strictly prohibited. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the view of the Company. Thank you.


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page