Try using the select column order like in ORDER BY 1, 2, 3, 4 etc.
OR
Try using the alias name in the select statement like in ORDER BY
ReleaseDate, VersionID etc
-Kavitha
-----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