Link to home
Start Free TrialLog in
Avatar of westdh
westdhFlag for United States of America

asked on

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

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'
ASKER CERTIFIED SOLUTION
Avatar of sventhan
sventhan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of lludden
lludden
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of westdh

ASKER

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.
Avatar of westdh

ASKER

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