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 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.
Answer :
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
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;