Try order by ReleaseDate



-----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