Higher value comparing rows

Navigation
3

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 would like my query to return the following table: 

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

That is, I want you to return the records with the largest values in the NUMBER column between rows, regardless of how many records exist.

    
asked by anonymous 24.04.2018 / 16:43

2 answers

4

I think it would look 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

    
24.04.2018 / 17:10
0

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;
    
25.04.2018 / 01:04
Doubt about pointers in C Position div or button inside at a specific point in an image