title: Why distinct is not working at this sql query ? - complex query
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/8173137/distinct-in-sql-server">Distinct in SQL Server</a>  </p>
  </blockquote>
  
  
  
  <p>I want to select disctinct PokemonId variables but it is not working</p>
  
  <p>here the query</p>
  
  <pre><code>select distinct top 36 
      tblFoundPokemonsOnRoutes.pokemonId,MonsterTotalStats,
      MAX(maxLevel) as maxLevel,
      Case Class WHEN 'Ancient' Then '9' 
        WHEN 'Legendary' Then '8' 
        WHEN 'Zenith' Then '7' 
        WHEN 'Emissary' Then '6' 
        WHEN 'Starter' Then '5' 
        WHEN 'Superior' Then '4' 
        WHEN 'Regular' Then '3' 
        ELSE Class 
      END as Result 
  from 
      tblFoundPokemonsOnRoutes 
  left join 
      tblPokedex on tblFoundPokemonsOnRoutes.pokemonId = tblPokedex.PokemonId 
  where 
      tblFoundPokemonsOnRoutes.routeId in 
            (select routeId from tblRoutes where ZoneNumber = 1) 
  group by 
      maxLevel, tblFoundPokemonsOnRoutes.pokemonId, MonsterTotalStats, Class 
  order by 
      Result desc, MonsterTotalStats desc
  </code></pre>
  
  <p>Here the returned results</p>
  
  <p><img src="https://i.stack.imgur.com/0kDFu.png" alt="enter image description here"></p>
  
  <p>thanks a lot for the answers</p>