CODEWITHSUNDEEP
sql
Wai Wong
Wai Wong

Reputation: 2843

Select max value of each group

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.

Upvotes: 142

Views: 462621

Answers (7)

twk7890
twk7890

Reputation: 501

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

Upvotes: 49

John Hartsock
John Hartsock

Reputation: 86882

SELECT
  b.name,
  MAX(b.value) as MaxValue,
  MAX(b.Anothercolumn) as AnotherColumn
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
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)

Upvotes: 29

Fabien Haddadi
Fabien Haddadi

Reputation: 2080

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;

Upvotes: 0

Lilit Galstyan
Lilit Galstyan

Reputation: 81

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.

Upvotes: 8

Umair Sheikh
Umair Sheikh

Reputation: 39

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

Upvotes: 3

m.edmondson
m.edmondson

Reputation: 30882

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

Upvotes: 226

Muhammad Jahanzeb
Muhammad Jahanzeb

Reputation: 1

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.

Upvotes: -4

Related Questions