[SQL] 각 그룹의 최대 값을 선택

각 그룹의 최대 값을 선택

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

내 표는 다음과 같이 보입니다. 나는 각 펌프에 대한 최대 값을 선택하는 방법을 알고 싶습니다.

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

이 코드는 일을하지만 같은 값을 가진 두 개의 항목이 있기 때문에 내가 펌프 (1)의 두 항목을 얻을.

해결법

1. ==============================

   1.

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

2. ==============================

   2.

   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
   

   기본 키가 있다면이 훨씬 쉬울 것이다합니다. 다음 예는

   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) 
   

3. ==============================

   3.

   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
   

4. ==============================

   4.

   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
   

   이 같은 시도, 그것은 작동합니다.

5. ==============================

   5.

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

6. ==============================

   6.

   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
   

   이것은 당신에게 아이디어를 줄 것이다.

from https://stackoverflow.com/questions/4510185/select-max-value-of-each-group by cc-by-sa and MIT license