SQL Select column with maximum value in another column

Question

I have a table that looks like this:

Name  Group   Value
A     1       0
B     1       2
C     1       5
D     2       6
E     2       0
F     3       3

I want to select the name with the maximum value within each group. For example, there are 3 groups, so the result would be:

Name
C     (because it has the maximum value (5) within group 1)
D     (because it has the maximum value (6) within group 2)
F     (because it has the maximum value (3) within group 3)

I tried writing something like this:

SELECT name FROM table
WHERE value = (SELECT max(value) FROM table)
GROUP BY group

But max(value) returns the global maximum value of the whole table (like (6) in this example). How do I fix this?

Answer 1

There are many ways to do this, here are some of them:

rextester for all of these: http://rextester.com/DTWB67044

max() over() version:

with cte as (
  select *, MaxValue = max([Value]) over (partition by [Group])
  from t
)
select Name
from cte
where [Value] = MaxValue;

inner join version:

select t.Name
from t
  inner join (
    select MaxValue=max(value), [Group]
    from t
    group by [Group]
    ) as m
      on t.[Group] = m.[Group]
     and t.[Value] = m.MaxValue;

cross apply() version:

select t.Name
from t
  cross apply (
    select top 1
        [Value]
      from t as i
      where i.[Group] = t.[Group]
      order by i.[Value] desc
     ) as x
  where t.[Value] = x.[Value];

---

If you will only be returning one value per group, these work as well:

common table expression with row_number() version:

with cte as (
select *, rn = row_number() over (partition by [Group] order by [Value] desc)
from t
)
select Name
from cte
where rn = 1;

top with ties version:

select top 1 with ties 
  t.Name
from t
order by row_number() over (partition by [Group] order by [Value] desc);

Answer 2

You are sort of close. You need a correlated subquery instead of aggregation in the outer query:

SELECT t.*
FROM table t
WHERE value = (SELECT max(t2.value) FROM table t2 WHERE t2.group = t.group);

This is standard SQL and will work in any database. You can just select name if that is all you want, but I figure the group would also be useful.

In most databases, you would use row_number() for this purpose.

In SQL Server, it would be more typical to do:

select t.*
from (select t.*,
             row_number() over (partition by group order by value desc) as seqnum
      from t
     ) t
where seqnum = 1;

If there are ties (for the maximum), this returns exactly one row (often what is desired). If you want all such rows, use rank() or dense_rank() instead.