Select max value of each group

Question

Name    Value   AnotherColumn
-----------
Pump 1  8000.0  Something1
Pump 1  10000.0 Something2
Pump 1  10000.0 Something3
Pump 2  3043    Something4
Pump 2  4594    Something5
Pump 2  6165    Something6

My table looks something like this. I would like to know how to select max value for each pump.

select a.name, value from out_pumptable as a,
(select name, max(value) as value from out_pumptable where group by posnumber)g where and g.value = value

this code does the job, but i get two entries of Pump 1 since it has two entries with same value.

score 217 · Answer 1 · edited May 12 '15 at 20:46

217

select name, max(value)
from out_pumptable
group by name

edited May 12 '15 at 20:46

Paul Richter

10,908
10
52
85

answered Dec 22 '10 at 14:32

m.edmondson

30,382
27
123
206

60

But this will not work on Postgres or any other RDBMS with strict `GROUP BY`. In strict `GROUP BY` every column in your `SELECT` must either appear in your `GROUP BY` or be used in an aggregate function. – NickAb Dec 07 '14 at 03:56
so what is the solution for non mysql rdbms? – Toskan Sep 30 '16 at 21:35
actually this doesn't even work correctly for me in mysql. It won't always return the record with the max value of the group. – Toskan Oct 02 '16 at 02:55
5

Right. "Works in MySQL" in this context basically means it doesn't crash, not that it necessarily returns the correct result. – Craig Tullis Oct 07 '16 at 22:38
3

@Craig wrong, this query works in all flavours of rdbms and returns the data as expected, since the OP was not after returning the entire record with the maximum value per group, but wanted the max value per pump. The select list has 2 fields: name and value. Name is in the group by clause and value is aggregated via max. I cannot see any version of the answer where there would have been more fields in the select list. – Shadow May 02 '17 at 09:00
@Shadow MySQL group by does not behave the same as stricter database engines. It will return results in cases where other databases won't, which can lead to a false sense that you got the right answer. – Craig Tullis May 02 '17 at 17:51
1

@Craig wrong again. Mysql's group by behaviour is configurable since v4.0. However, this does not matter in this particular case, since the code in this answer complies with the sql standard. – Shadow May 02 '17 at 19:48
@Shadow you're nothing if not abrasive. BTW, that configurability won't matter in 99.9% of cases because it isn't default behavior. – Craig Tullis May 02 '17 at 19:50
3

@Craig it **was** not the default behaviour. It was changed in v5.7.5, almost 3 years ago. But again, you miss the point: **the code in this answer complies with the sql standard, therefore mysql's group by setting is irrelevant.** – Shadow May 02 '17 at 20:07
20

@NickAb Am I missing something? Every column *is* either in the group by or in an aggregation function – Rob Jan 26 '18 at 09:14
This answer doesn't account for the duplication element that the OP mentions... you will still get a duplicate for Pump 1 as both records match the MAX criteria – Gallus Mar 06 '19 at 12:07
if want another fields as well in output, then suggested method will not work. Might want to have a look at this: https://www.eversql.com/select-max-min-last-row-for-each-group-in-sql-without-a-subquery/ – Khatri Jun 28 '19 at 20:24
@Shadow: Trying to accomplish something similar in PostgreSQL 13.0, but it appears Craig is still/again right: "Value" must appear in the GROUP BY clause or be used in an aggregate function. This must be extremely common, but I seem to be unable to find a solution. – Tim Apr 22 '21 at 12:07
@Tim value field **is used** in the aggregate max() function in this answer, so I do not really know what your comment is about. – Shadow Apr 22 '21 at 12:10
@Shadow: I apologize I should have been more clear. If one has **MAX(Value)** int the **SELECT** clause and one were to omit **Value** from the **GROUP BY** clause, it WILL generate an error: _"Value" must appear in the GROUP BY clause or be used in an aggregate function._ It appears the error message should read something like: _"Value" must appear in the GROUP BY clause when used in an aggregate function._ – Tim Apr 23 '21 at 13:46
@Tim no, that's wrong. Value field is aggregated, therefore it does not have to be in the group by clause. – Shadow Apr 23 '21 at 14:48
@Shadow: I am terribly sorry. I thought my situation was similar, but no: ``` SELECT Name, MAX(value), AnotherColumn, FROM value GROUP BY name; ``` Produces the error. And the desired result would be: Name MAX(Value) AnotherColumn ----------- Pump 1 10000.0 Something2 Pump 1 10000.0 Something3 Pump 2 6165 Something6 Basically, I desire all the features of the MAX(value), not just the GROUP BY feature. But, I guess that might be something for a new/other topic. – Tim Apr 24 '21 at 13:37
@Tim see https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column for lots of different answers for that problem. – Shadow Apr 24 '21 at 13:42

score 41 · Answer 2 · edited Jan 26 '18 at 09:12

41

select name, value 
from( select name, value, ROW_NUMBER() OVER(PARTITION BY name ORDER BY value desc) as rn
from out_pumptable ) as a
where rn = 1

edited Jan 26 '18 at 09:12

Rob

26,989
16
82
98

answered Jan 26 '18 at 09:03

twk7890

421
4
3

2

This is the only example that works for my case. I have multiple values per 'enrollment'. What I need is the last value for each enrollment, left outer joined. Ordered by `id DESC` on the `PARTITION` and wrapped this query in a `LEFT OUTER JOIN as grades ON grades.enrollment_id = enrollment.id` and works perfectly. – lucasarruda Mar 26 '19 at 21:34

John Hartsock · Answer 3 · 2010-12-22T15:11:52.933

29

SELECT
  b.name,
  MAX(b.value) as MaxValue,
  MAX(b.Anothercolumn) as AnotherColumn
FROM out_pumptabl
INNER JOIN (SELECT 
              name,
              MAX(value) as MaxValue
            FROM out_pumptabl
            GROUP BY Name) a ON 
  a.name = b.name AND a.maxValue = b.value
GROUP BY b.Name

Note this would be far easier if you had a primary key. Here is an Example

SELECT * FROM out_pumptabl c
WHERE PK in 
    (SELECT
      MAX(PK) as MaxPK
    FROM out_pumptabl b
    INNER JOIN (SELECT 
                  name,
                  MAX(value) as MaxValue
                FROM out_pumptabl
                GROUP BY Name) a ON 
      a.name = b.name AND a.maxValue = b.value)

edited Dec 22 '10 at 15:11

answered Dec 22 '10 at 14:31

John Hartsock

85,422
23
131
146

1

Ah, dang. I made the example too simple. There more columns the table, which make it a little complicated >. – Wai Wong Dec 22 '10 at 14:33
If theres more columns just add them into the select – m.edmondson Dec 22 '10 at 14:34
Can you elaborate on the columns. What are you trying to group by? – John Hartsock Dec 22 '10 at 14:34
@Wai Wrong I see that you added another column but you need to explain what you want to do with it? Do you want to return a value from that column? Do you want to group by name and SomeOtherColumn? – John Hartsock Dec 22 '10 at 14:37
Added another column. I want to get the max value for each pump along with the values that are in the max value's row from the other columns. Am I getting too confusing? – Wai Wong Dec 22 '10 at 14:38
@Wai Wong Do you have a primary key or unique key on the table? If so could you add it to your Question. Also the when there are identical values for a group. Which one do you want returned? – John Hartsock Dec 22 '10 at 14:38
@Wai Wong. When value Columns are identical which one do you want returned? – John Hartsock Dec 22 '10 at 14:41
It doesn't matter which one is returned. As long as it only shows one for each pump – Wai Wong Dec 22 '10 at 14:43
@Wai Wong ... Give that a Try and let me know – John Hartsock Dec 22 '10 at 14:47
@Wai Wong... I forgot to include the extra column in the query and I had a few syntax errors. Take a look at the revised code. – John Hartsock Dec 22 '10 at 14:56
I think I got back to square one. The query gives the same result as your first answer. But thanks anyway – Wai Wong Dec 22 '10 at 14:57
@JohnHartsock You forgot to give alias b to the first table. – Fabien Haddadi May 31 '21 at 08:16

score 7 · Answer 4 · answered Aug 15 '18 at 07:13

7

select Name, Value, AnotherColumn
from out_pumptable
where Value =
(
  select Max(Value)
  from out_pumptable as f where f.Name=out_pumptable.Name
)
group by Name, Value, AnotherColumn

Try like this, It works.

answered Aug 15 '18 at 07:13

Lilit Galstyan

71
1
1

The query has crashed on my table that contains tens of thousands of rows :) – RAM237 Feb 18 '21 at 09:32

score 2 · Answer 5 · answered Jun 05 '18 at 20:37

2

select * from (select * from table order by value desc limit 999999999) v group by v.name

answered Jun 05 '18 at 20:37

Umair Sheikh

29
1

very clever solution – Dawid D Feb 10 '21 at 21:17
Lol, the query crashed on my table that contains tens of thousands of rows :) – RAM237 Feb 18 '21 at 09:24

score 2 · Answer 6 · answered Jan 24 '22 at 06:46

Using analytic function is the easy way to find max value of every group.

Documentation : https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15

Select name, 
       value,
       AnotherColumn
From(
    SELECT Row_Number() over(partition by name order by value desc)as 
   row_number, *
FROM students
) 
 Where row_number = 1

score 0 · Answer 7 · answered May 31 '21 at 08:40

SELECT t1.name, t1.Value, t1.AnotherColumn
FROM mytable t1
JOIN (SELECT name AS nameMax, MAX(Value) as valueMax 
      FROM mytable 
      GROUP BY name) AS t2 
    ON t2.nameMax = t1.name AND t2.valueMax = t1.Value
WHERE 1 OR <anything you would like>
 GROUP BY t1.name;

score -4 · Answer 8 · edited Oct 08 '11 at 22:36

-4

SELECT DISTINCT (t1.ProdId), t1.Quantity FROM Dummy t1 INNER JOIN
       (SELECT ProdId, MAX(Quantity) as MaxQuantity FROM Dummy GROUP BY ProdId) t2
    ON t1.ProdId = t2.ProdId
   AND t1.Quantity = t2.MaxQuantity
 ORDER BY t1.ProdId

this will give you the idea.

edited Oct 08 '11 at 22:36

sjngm

12,423
14
84
114

answered Oct 08 '11 at 11:28

Muhammad Jahanzeb

1

Select max value of each group

8 Answers8

Linked

Related