Return ID row matching the minimum value in another column

Question

Currently this is what I have -

select ServiceName, MethodName, 
       min(duration) AS duration, min(ID) as ID, count(id) As count
from dbo.log
group by ServiceName, MethodName

I need the ID returned to match the min(duration) value. Currently I'm not sure what ID it is returning but it's not the one where the duration is the lowest.

What do you want in the case there are multiple rows with the same "minimum" value? Which one do you pick? — The Impaler, May 31 '18 at 18:20

score 1 · Answer 1 · answered May 31 '18 at 18:20

You should try query like below. The query uses Row_number() but you can also use rank() in case you need multiple rows in case many records have same minimum duration . See a nice link on explanation

The query below however will return a single row irrespective of many rows having same minimum duration for same service name and method name

select
 ServiceName, MethodName,id,ct,duration
from
(
select *, 
       rn=row_number() over( partition by ServiceName, MethodName order by duration asc),
       ct=count() over (partition by  ServiceName, MethodName)
from dbo.log)t
where rn=1

Daniel Gimenez · Accepted Answer · 2018-06-01T12:20:08.897

If you want the min duration and its id at the same time then the simplest way to do this is to use two window functions.

SELECT DISTINCT ServiceName, MethodName
    , MIN(Duration) OVER (PARTITION BY ServiceName, MethodName ORDER BY Duration ASC)
    , FIRST_VALUE(Id) OVER (PARTITION BY ServiceName, MethodName ORDER BY Duration ASC)
FROM log

It may seem unnecessary to have the ORDER BY clause for the MIN of duration, however by reusing that partition both functions can be processed simultaneously and not have to be divided into separate sets and recombined. The best way to understand is to take the order by out and view the query plan and see how it adds Nested Loops and a lot of other extra steps. Long story short, this ends up producing a pretty short and efficient plan.

I hope it's obvious how the correct Id is retrieved. Basically this relies on the fact that sorting a set results in any value in the first row to be related to the min/max value(s) used in the sort.

If multiple Id's match the duration and you wanted to see them all, you could do the following instead. You can use TOP to limit the result to a certain number of rows.

SELECT l1.ServiceName, l1.MethodName, l1.Duration, x.Id 
FROM (
    SELECT ServiceName, MethodName, MIN(Duration) Duration 
    FROM log GROUP BY ServiceName, MethodName
) l1 
CROSS APPLY (
    SELECT TOP 10 Id 
    FROM log l2 WHERE l2.ServiceName = l1.ServiceName 
        AND l2.MethodName = l1.MethodName 
        AND l2.Duration = l1.Duration
) x

This is actually exactly what I needed. Thank you, returns just one value (lowest) and it doesn't matter which one. — Jeff Lohr, May 31 '18 at 21:56
Is there a way instead of using FIRST_VALUE, to maybe get like first 10 values? — Jeff Lohr, May 31 '18 at 22:54
Did you want up to 10 Id's that match the same duration? If so I modified my answer to support that — Daniel Gimenez, Jun 01 '18 at 12:20

score 0 · Answer 3 · answered May 31 '18 at 18:28

0

Just to add a different option, I think this one should also work:

with s as (
  select ServiceName, MethodName, 
      min(duration) AS duration, count(id) As count
    from dbo.log
    group by ServiceName, MethodName
)
select s.*, l.id
  from s 
  join dbo.log l on s.ServiceName = l.ServiceName 
                and s.Methodname = l.MethodName
                and s.duration = l.duration;

This query will show multiple rows for the same service, in the event there are multiple rows with the same minimum value.

answered May 31 '18 at 18:28

The Impaler

45,731
9
39
76

This works great for multiple instances of lowest duration. – Jeff Lohr May 31 '18 at 21:55
Yep, that's what I'm saying all along. – The Impaler May 31 '18 at 21:58

Return ID row matching the minimum value in another column

3 Answers3