mssql: I need to know how to sort 'order by' this DISTINCT Select statement

westdh
westdh used Ask the Experts™
on
I need to know how to sort 'order by' this DISTINCT Select statement:
to include order by state, city

SELECT DISTINCT Ext3 + ' ' + [State] + ' ' + [City] + ' ' + [Ext2]  AS MyData
  FROM [trip4usdnn].[trip4usdnn].[Netism_MapExtreme_Locations] where state = 'OR' or state = 'WA'
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
did you try this?

SELECT DISTINCT Ext3 + ' ' + [State] + ' ' + [City] + ' ' + [Ext2]  AS MyData
  FROM [trip4usdnn].[trip4usdnn].[Netism_MapExtreme_Locations] where state = 'OR' or state = 'WA'
order by state,city
SELECT DISTINCT Ext3 + ' ' + [State] + ' ' + [City] + ' ' + [Ext2]  AS MyData
  FROM [trip4usdnn].[trip4usdnn].[Netism_MapExtreme_Locations] where state = 'OR' or state = 'WA'
Order By [State], [City]

Author

Commented:
You guys did the same as I, that's why I called you experts:
well I tried you code and got the same error

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Thanks

Commented:
What was the issue then?
You can use:

with cteDistinct AS (
SELECT DISTINCT Ext3 + ' ' + [State] + ' ' + [City] + ' ' + [Ext2]  AS MyData, [City], [State]
  FROM [trip4usdnn].[trip4usdnn].[Netism_MapExtreme_Locations] where state = 'OR' or state = 'WA'
)
SELECT MyData FROM cteDistinct
ORDER BY [State], [City]

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial