title: How can I sort version numbers such as ‘A’, ‘B’, ‘AA’, ‘AB’ and ‘AAA’?
body: <p>In reference to this question: <a href="https://stackoverflow.com/questions/33353249/">How do I grab only the latest Invoice Number</a></p>
  
  <p>I accepted an answer that uses the MAX function but Robert McKee pointed out that will result in sorted values such as:</p>
  
  <pre>
  ‘A’
  ‘AA’
  ‘AAA’
  ‘AB’
  ‘B’
  </pre>
  
  <p>When what I need is:</p>
  
  <pre>
  ‘A’
  ‘B’
  ‘AA’
  ‘AB’
  ‘AAA’
  </pre>
  
  <p>I am trying to find a way to find the latest Version of an Invoice. The accepted answer from the referenced question will work up to a point. And it did satisfy my question... But now a new problem deserves its own question and not for me to go back and modify my original question. So…</p>
  
  <p>The only thing I have to work with is the Invoice Number itself.
  The Invoice number has a format of  #####XXX, where ##### is the actual Invoice Number and the XXX is the version number. XXX can be anywhere from ‘A’ to ‘ZZZ’.</p>
  
  <p>Here is my attempt to find a plausible work around (a sql test case):</p>
  
  <pre><code>DECLARE @TempTable TABLE (MyNumber int, MyString varchar(15));
  
  INSERT @TempTable
  VALUES (100, 'A'), (100, 'AAZ'), (100, 'B'), (100, 'AZ'), (100, 'C'), (100, 'Z'), (100, 'AA'), (100, 'AB');
  
  SELECT TOP 1
  RTRIM(CAST(MyNumber AS NVARCHAR(15)) + MyString) AS InvoiceNumber
  FROM @TempTable
  ORDER BY RIGHT(LEFT(MyString + SPACE(2), 3), 1) DESC, RIGHT(LEFT(MyString + SPACE(2), 2), 1) DESC, LEFT(MyString, 1) DESC;
  </code></pre>
  
  <p>Would anyone care to provide a better answer or point me in the right direction to clean mine up?</p>
  
  <p>Thanks in advance,</p>