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

User · Answer

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

User · Answer

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

User · Answer

select name  max value  from out pumptable group by name

User · Answer

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

User · Answer

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

User · Answer

select   from  select   from table order by value desc limit 999999999  v group by v name

[sql] Select max value of each group

Examples related to sql