Home / Questions / 56151
Accepted
eezzekl
Asked: 2020-03-18 13:16:17 +0800 CST 2020-03-18 13:16:17 +0800 CST

Top 1 for each group of a Group by SQL Server

  • 772

I have a table similar to this CancelLog

|ID| membrecia| User    | DateCancel  | Motivo |
| 2| 1        | uS1     | 7/29/2011   |  ...   |
| 3| 1        | uS2     | 7/30/2011   |   ...  |
| 6| 1        | uS1     | 8/02/2011   |   ...  |
| 1| 2        | uS1     | 7/28/2011   |   ...  |
| 4| 2        | uS2     | 7/30/2011   |  ....  |
| 5| 2        | uS3     | 8/01/2011   |    .   |
| 6| 3        | uS1     | 8/02/2011   |    .   |

and I would like to get The most recent record of each membership is to say something like this

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

I was doing something similar to this,

select l.membrecia, l.[USER] , l.DateCancel, count(*), max(l.DateCancel)
from CancelLog l
group by l.membrecia, l.[USER], l.DateCancel;

count(*) y max(l.DateCancel)I only use them for information, they will not be in the final query

if it gives me the most recent results, but if a membership is "cancelled"* by several different users, it brings me the data of all users who canceled it

 |ID| membrecia| User    | DateCancel  | Motivo |
 | 3| 1        | uS2     | 7/30/2011   |   ...  |
 | 6| 1        | uS1     | 8/02/2011   |   ...  |
 | 1| 2        | uS1     | 7/28/2011   |   ...  |
 | 4| 2        | uS2     | 7/30/2011   |  ....  |
 | 5| 2        | uS3     | 8/01/2011   |    .   |

I would like to get the records like the ones shown in table 2.

*During cancellation, the "Reason" can be changed, and due to this change a new record is created in the log.

change from motivo"Temporary removal" to "Definitive removal", so I am only interested in the last change regardless of the user, but I do need the last user who made the change in the results.

Does anyone know how I could get those logs?

sql-server

3 Answers

  1. What you are looking for is achieved very easily 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
    • 4

  2. Dear, I did an exercise to be able to obtain the data that you request in the way that you request it, I enclose a query of how I did it, perhaps it can be of use to you. 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

    Here the Query shows the result it delivers. Greetings.

    • 1
  3. Best Answer

    Thanks to those who helped, but I managed to solve it as follows

    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, getting this highest id, I compare it with the main query id and I got 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   |    .   |
    • 0