Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
archsea
Starting Member
2 Posts |
 Posted - 2015-02-20 : 18:55:38
|
| Error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified" when using following query:SELECT DISTINCT [PhysicalZip] = LEFT([PhysicalZip], 5) FROM [DATABASENAME].[dbo].[tblSchSchools] SCH JOIN dbo.tblSchSchoolTyp TYP ON SCH.[SchoolType] = TYP.[Type] WHERE (SCH.[Deleted] = 'False' AND TYP.[Deleted] = 'False') AND SCH.[PhysicalZip] IS NOT NULL ORDER BY SCH.[PhysicalZip]Worked in 2005 but not in 2008 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-02-20 : 18:57:28
|
| SELECT DISTINCT [PhysicalZip] = LEFT([PhysicalZip], 5)FROM [DATABASENAME].[dbo].[tblSchSchools] SCHJOIN dbo.tblSchSchoolTyp TYP ON SCH.[SchoolType] = TYP.[Type]WHERE (SCH.[Deleted] = 'False' AND TYP.[Deleted] = 'False')AND SCH.[PhysicalZip] IS NOT NULLORDER BY [PhysicalZip]Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
archsea
Starting Member
2 Posts |
Posted - 2015-02-20 : 19:43:52
|
| That worked - such a simple fix - thanks, you are the best. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-02-20 : 19:45:38
|
| You're welcome, glad to help. The PhysicalZip in the ORDER BY is referencing the alias in the SELECT. If it had said SELECT DISTINCT SomeOtherAliasName = LEFT..., then you'd have had to use SomeOtherAliasName in the ORDER BY.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
SELECT DISTINCT