get record from every group

Question

I have a table-messages that contains some data like blow picture:

I want to get the follwing result,

Can someone help me to implement a replacement scheme, I have tried it, it is very inefficient for me, I don't like it very much.
SELECT
*
FROM (SELECT
*
FROM messages
ORDER BY id DESC) AS x
GROUP BY name

Answer 1

Hi，@newbie

Welcome to Microsoft T-SQL Q&A Forum!

Try both options, if you want the record to always be the last in ID order. This will help you.

--first way  
  
select a.*  
from  messages a  
inner join   
(  
  select name, max(id) as maxid from messages group by name  
) b   
  on  a.id = b.maxid  
  
--second way  
  
;WITH CTE AS   
(  
  SELECT Id, Name, OtherColumns,rn=ROW_NUMBER() OVER ( PARTITION BY Name ORDER BY Id )   
  FROM messages  
)  
  SELECT Id, Name, OtherColumns  
  FROM CTE  
  WHERE rn = 1;  

Best regards,
Bert Zhou