Top 1 for each group in a Group by SQL Server

Thanks to those who helped, but I managed to solve it in the following way

select l.membrecia, l.[USER] , l.DateCancel
from CancelLog l
where id = (select top 1 lg.id from CancelLog lg
       where lg.membrecia = l.membrecia order by lg.id desc)
)

As you can see I made a subQuery, which I get the most recent id of a given membership, ordering it from highest to lowest, obtaining this id that is higher, I compare it with the main query id and I obtained the desired result

|ID| membrecia| User    | DateCancel  | Motivo |
| 6| 1        | uS1     | 8/02/2011   |   ...  |
| 5| 2        | uS3     | 8/01/2011   |    .   |
| 6| 3        | uS1     | 8/02/2011   |    .   |

What you are looking for is achieved very simply by using the window function row_number() . There is no need to use temporary tables and loops, which is too complicated and expensive for this type of query:

select c.ID, c.membrecia c.[User], c.DateCancel, c.Motivo
  from (select c.*,
               row_number() over (
                   partition by c.membrecia
                       order by c.DateCancel desc) as rn
          from CancelLog c) c
 where c.rn = 1

Dear, I did an exercise to be able to obtain the data you request in the way you request it, I attach a query on how I did it, maybe it could be useful. Greetings.

Here the Query.

BEGIN
DECLARE @contador INT = 1;
CREATE TABLE #temporal (id INT PRIMARY KEY IDENTITY(1,1), [User]  VARCHAR(10));
CREATE TABLE  #datos (membrecia INT, [User] VARCHAR(20), DateCancel DATE, Motivo VARCHAR(100));
INSERT INTO #temporal SELECT DISTINCT [USER] FROM CancelLog 

WHILE @contador < (SELECT COUNT(*) FROM #temporal)+1
BEGIN
INSERT INTO #datos SELECT 
    membrecia, 
    [USER] ,
    DateCancel,
    Motivo
    FROM CancelLog 
    WHERE DateCancel = (SELECT MAX(DateCancel) FROM CancelLog WHERE [User] =  (SELECT [USER] FROM #temporal WHERE id = @contador)) 

    SELECT @contador +=1;
END
SELECT * FROM #datos
DROP TABLE #temporal
DROP TABLE #datos 
END