Query [SQL Server] Aggregate field with equal values of a later date and bring other attributes referring to that date

Question

I would like to help to assemble a query to aggregate the same phone numbers and bring the latest date, in addition to bring other attributes referring to that date in SQL Server.

I built this query :

SELECT [Telefone], MAX([data_cadastro]), count() FROM blacklist GROUP BY [Telefone] having Count()>0 order by [telefone];

It aggregates the same phones and brings back the latest date, but the problem is to recover the other attributes referring to the phone number X of the latest date.

Original tebela:

Query result:

Answer 1

One way to do what you want is to use a merge of the original table with the result of the query you’ve already assembled. Something like this:

SELECT a.* 
FROM blacklist a, (SELECT Telefone, MAX(data_cadastro) data_cadastro FROM blacklist GROUP BY Telefone) b
WHERE a.Telefone = b.Telefone
    AND a.data_cadastro = b.data_cadastro;