Top 2 of each category-CodePudding

I need top 2 pokemons of each type. I have this code:

SELECT 
 [Name], 
 [Type 1],
 Total,
 rank() OVER(partition by [Type 1] order by Total,Hp,Attack,Defense,Speed,# desc) AS Ranking
FROM PokemonProject..Pokemon

The result table is like this:

<table>
<thead>
<tr>
<th>Name    </th>
<th>Type 1</th>
<th>Total   </th>
<th>Ranking</th>
</tr>
</thead>
<tbody>
<tr>
<td>Xerneas</td>
<td>Fairy</td>
<td>680</td>
<td>1</td>
</tr>
<tr>
<td>Florges</td>
<td>Fairy</td>
<td>552</td>
<td>2</td>
</tr>
<tr>
<td>Ho-oh           </td>
<td>Fire</td>
<td>680</td>
<td>1</td>
</tr>
<tr>
<td>CharizardMega Charizard X           </td>
<td>Fire</td>
<td>634</td>
<td>2</td>
</tr>
<tr>
<td>CharizardMega Charizard Y           </td>
<td>Fire</td>
<td>634</td>
<td>3</td>
</tr>
</tbody>
</table>

I know i need a WHERE clause, but i don know how to do it. Something like "Where ranking <= 2" but when i run SQL it says: "Invalid column name 'ranking'" Im new at SQL.

CodePudding user response：

Given your query returns the result you are expecting, below is one way you can apply the where clause:

WITH cte AS 
(
    SELECT 
     [Name], 
     [Type 1],
     Total,
     rank() OVER(partition by [Type 1] order by Total,Hp,Attack,Defense,Speed,# desc) AS Ranking
    FROM PokemonProject..Pokemon
)    
 WHERE Ranking <= 2