SQL get all names that have their MAX() where month='jan'

Question

I'm searching a SQl query that gives me all values that have their max() at a certain time.

This is my simplified table:

    +---------+-------+-------+
    | name    | value | month |
    +---------+-------+-------+
    |  joe    |   10  | jan   |
    |  olivia |   300 | jan   |
    |  tim    |   10  | jan   |
    |  joe    |   100 | feb   |
    |  olivia |   50  | feb   |
    |  tim    |   100 | feb   |
    +---------+-------+-------+

This is how I would describe my query:

SELECT name FROM table WHERE MAX(value) is in 'jan'

This should be my result:

olivia

Best Joerg

score 4 · Accepted Answer · answered Sep 05 '19 at 11:42

Here is one method:

select t.*
from t
where t.value = (select max(t2.value) from t t2 where t2.name = t.name) and
      t.month = 'jan';

You can also use window functions:

select t.*
from (select t.*,
             max(value) over (partition by name) as max_value
      from t
     ) t
where value = max_value and month = 'jan'

Zaynul Abadin Tuhin · Answer 2 · 2019-09-05T11:44:55.563

1

you need below

SELECT name FROM table WHERE value = (select max(value) from table where month='jan') and month='jan'

incase of month wise max

select t1.* from table_name t1
where value = (select max(value) from table_name t2 where t1.month=t2.month)

edited Sep 05 '19 at 11:44

answered Sep 05 '19 at 11:40

Zaynul Abadin Tuhin

31,407
5
33
63

You need a correlated subquery. – jarlh Sep 05 '19 at 11:41
@jarlh is olivia his expected result? – Zaynul Abadin Tuhin Sep 05 '19 at 11:42
If the table also had the row (charlie, 300, October), it would be returned. – jarlh Sep 05 '19 at 11:44
@jarlh i have added another and condition for month – Zaynul Abadin Tuhin Sep 05 '19 at 11:46

SQL get all names that have their MAX() where month='jan'

2 Answers2