title: Order by issue in Common Table Expression
body: <p>I am using Common Table Expression in stored procedure shown below I want to display the country name in ascending order </p>
  
  <pre>
  1   | India
  2   | Bangladesh
  3   | Srilanka
  4   | Nepal
  5   | Japan
  </pre>
  
  <p>The expression is as follows</p>
  
  <pre><code>WITH CTECountry as
  (
  SELECT 
     CountryID,
     CountryName 
  FROM dbo.Country
  ORDER BY CountryName ASC        
  )
  SELECT 
  *
  FROM CTECountry 
  UNION SELECT '0','--Select--'
  </code></pre>