[Solved] SQL Select column with maximum value in another column

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:

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?

Solution #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
      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 
from t
order by row_number() over (partition by [Group] order by [Value] desc);
Respondent: user3685285

Solution #2:

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

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.

Respondent: SqlZim

Solution #3:

Gordon’s solution appliead to an example from user3685285:

FROM table t
WHERE Value = (SELECT max(tt.Value)
FROM table tt
WHERE tt.Group = t.Group);

Respondent: Gordon Linoff

Solution #4:

This will give you the desired output.

Select name, max(value) from table group by group order by group 


C | 5
D | 6
F | 3
Respondent: gajama

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.