title: SQL select top five most recent row and distinct by a specific column
body: <p>Ok, So say I have a table as picture below name appModelFlat only with a few hundred more rows. It does not have a date field but I want to find out the five most recently created environments (EnvName). There is only 14 possible environments (EnvName). But I want to select the five most recently inserted rows that inserted different EnvName. That is to say I want to select distinct EnvName (Although distinct doesn't work this way) most recent 5 rows , and I know they are the most recent by their id. The higher the id the newer the row is. Any help on this query would be appreciated. </p>
  
  <pre><code>id|AppName|EnvName|ServerTypeName|ServerId|OS     |OSVersion|CPU|Memory|ExtraStorage|MachineDesc   |
  ----------------------------------------------------------------------------------------------------
  1 |ASB    |DEV    |App           |1       |Windows|7        |4  |4     |100         |ASB-DEV-App   |
  
  ----------------------------------------------------------------------------------------------------
  5 |AMS    |DEV    |APP           |2       |RedHat |7.2      |4  |4     |50          |AMS-DEV-App   |
  
  ----------------------------------------------------------------------------------------------------
  6 |SPB    |TST    |App           |1       |Windows|7        |2  |8     |50          |SPB-TST-App   |
  
  ----------------------------------------------------------------------------------------------------
  7 |SBI    |TST    |Oracle        |1       |Solaris|11       |4  |8     |100         |SBI-TST-Oracle|
  
  ----------------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>Here is my first attempt although I'm not sure if it is right. It does give me five results.</p>
  
  <pre><code> SELECT  DISTINCT top 5  [ID] = ( SELECT TOP 1 [ID]  FROM [AppModelFlat] Y WHERE Y.[EnvName] = X.[EnvName])
              ,[AppName]= ( SELECT TOP 1 [AppName] FROM [AppModelFlat] Y WHERE Y.[EnvName] = X.[EnvName])
              ,[EnvName] 
              ,[ServerTypeName] = ( SELECT TOP 1 [ServerTypeName] FROM [AppModelFlat] Y WHERE Y.[EnvName] = X.[EnvName])
              ,[ServerId] = ( SELECT TOP 1 [ServerId] FROM [AppModelFlat] Y WHERE Y.[EnvName] = X.[EnvName])
              ,[OS] = ( SELECT TOP 1 [OS] FROM [AppModelFlat] Y WHERE Y.[EnvName] = X.[EnvName])
          FROM [AppModelFlat] X order by id desc
  </code></pre>
  
  <p>edit:</p>
  
  <p>For expected result. Lets say I only wanted to select the top 2 since I only gave 5 entries here. I would want to get back the following.</p>
  
  <pre><code>5 |AMS    |DEV    |APP           |2       |RedHat |7.2      |4  |4     |50          |AMS-DEV-App   |
  
  ----------------------------------------------------------------------------------------------------
  7 |SBI    |TST    |Oracle        |1       |Solaris|11       |4  |8     |100         |SBI-TST-Oracle|
  </code></pre>
  
  <p>Because I only have one of each EnvName and each row has the highest Id number for that row.</p>