Higher value comparing lines

Question

I’m using SQL Server 2008 R2 and I have the following table:

ID  NUMERO      NOME   MODELO   
--- ----------- ------ -------
1   12          A      777 
2   23          A      777
3   05          A      777        
4   45          B      999  
5   65          C      555
6   30          B      999 
7   67          B      999
8   80          C      555
9   51          C      555
10  03          C      555

I wish my query return to the following table:

ID  NUMERO      NOME   MODELO
--- ----------- ------ -------
2   23          A      777
7   67          B      999
8   80          C      555

That is, I want to return the records with the highest values of the NUMERO column between the lines, regardless of how many records there are.

Answer 1

I think it would be something like this

SELECT A.* FROM Tabela A INNER JOIN (
SELECT MAX(numero) as numero, nome FROM Tabela GROUP BY nome
) B ON A.nome = B.nome AND A.numero = B.numero ORDER BY nome

Hugs

Answer 2

Another solution:

-- código #1
with tabela2 as (
SELECT *, 
       Seq= row_number() over (partition by NOME order by NUMERO desc)
  from tabela
)
SELECT ID, NUMERO, NOME, MODELO
  from tabela2
  where Seq = 1;