title: SQL Server : how to select last 3 rows in the table ordered by desc and then sort them asc?
body: <p>I have a CTE with the last 3 rows of the table sorted by <code>VersionNumber DESC</code>. How I can sort them backwards in order to have the smallest <code>VersionNumber</code> from those 3 always to have display order 1? </p>
  
  <p>There is no <code>LIMIT</code> in Microsoft SQL Server... </p>
  
  <pre><code>;WITH cte AS
  (       
      SELECT  
          BSId, RevisedBSId, VersionNumber, 
          RANK() OVER (ORDER BY VersionNumber DESC) AS [DisplayOrder] 
      FROM
          cte
  ),
  sortedctethree AS
  (
      SELECT TOP 3 
          BSId, RevisedBSId, VersionNumber, DisplayOrder 
      FROM 
          sortedcte
  )
  SELECT *
  FROM sortedctethree
  </code></pre>
  
  <p>Here <code>DisplayOrder</code> is wrong. If I use another CTE where I am trying to reorder, it is still wrong.</p>
  
  <p>I need only 3 rows. But in the TOP 3 can be also one, or two rows in the result. I need in that case always have the <code>DisplayOrder</code> 1 for the smallest VersionNumber.</p>
  
  <pre><code>VersionNumber | DisplayOrder 
  --------------+--------------
       2        |      2
       1        |      1
  </code></pre>
  
  <p>How it is possible to achieve? Thanks a lot in advance. </p>