Query with Group and name sorting

I am trying to build a SQL query that is bit more complicated to what I am used to writing.

Example data:

event_id    event_source duration   name
67538576    9071         67         [Lpr] abc
67371890    9071         62         [Lpr] Test data
67338632    9071         63         [Lpr] Test data
67331147    9071         63         [Lpr] Test data
67325698    9071         235        [Lpr] Test data
63098403    9071         2733441    [Lpr] Test data diff
63023403    8001         2241       [Cam] Some name
63013203    8001         212        [Cam] Some name
62013203    7000         60         [Lpr] Name B
62012203    7000         70         [Lpr] Name B

So far I have a working query

SELECT min(name) as name ,sum(duration) as TotalDuration
FROM table
WHERE duration > 30 AND name LIKE '[[]Lpr]%'
GROUP BY event_source
ORDER BY TotalDuration Desc

Currently it will return

Name            TotalDuration
[Lpr] Test data 2733931
[Lpr] Name B    130

But I would like that query to always returns the names with biggest event_id, so good results would be “[Lpr] abc” and “[Lpr] Name B”.

Answer

I think so

SELECT  T1.name,
        T2.TotalDuration
FROM table T1  INNER JOIN 
( SELECT max(event_id) as event_id,sum(duration) as TotalDuration
  FROM table
  WHERE duration > 30 AND name LIKE '[[]Lpr]%'
  GROUP BY event_source ) T2 
ON T1.event_id = T2.event_id 
ORDER BY T2.TotalDuration Desc

or you can use the window function win function sum

Sorry for the mistake 🙂