title: How to order by length inside WITH clause SQL Server 2008?
body: <p>Order by Length is not working inside the WITH clause.</p>
  
  <pre><code>;WITH Invalids AS
  (
      SELECT TOP 1 WITH TIES
          GroupNumber, 
          RTRIM(Address1) Address1, 
          RTRIM(Postcode) Postcode
      FROM dbo.LoadData WHERE GroupNumber NOT IN
      (
          '000','016','018','025','044','048','049','053','054','055','060','064','067','069','071','073'
      )
      ORDER BY ROW_NUMBER() OVER (PARTITION BY GroupNumber ORDER BY LEN(Address1) DESC, LEN(Postcode) DESC )
  )
  UPDATE dbo.LoadData
  SET 
      Valid = 0, 
      Reason = Reason + 'Bad address; '
  WHERE GroupNumber = 
  (
      SELECT GroupNumber FROM Invalids WHERE LEN(Address1) = 0 OR LEN(Postcode) = 0
  </code></pre>