Sink Sink - 3 years ago 200
SQL Question

MS SQL query count of the minimum value for distinct names in a table

I have the following result from a query and i want to make a subquery that counts the number of the minimum value in each name. for example:

Marcus 25
Marcus 27
Marcus 25
Jonathan 36
Jonathan 36
Jonathan 36
Jonathan 38


And the result should be :

Marcus 25 2
Jonathan 36 3


Any ideas?

Answer Source

One method uses window functions:

select t.name, count(*)
from (select t.*,
             rank() over (partition by name order by val) as seqnum
      from t
     ) t
where seqnum = 1;

EDIT:

Oh, the OP also wants the value:

select t.*
from (select t.name, t.val, count(*) as cnt,
             row_number() over (partition by name order by val) as seqnum
      from t
      group by t.name, t.val
     ) t
where seqnum = 1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download