SQL Get Other Rows From Aggregate Function

mysql, sql

Solution

You will want to use a subquery that will get the `max(b)` by each `A` and then join that value back to your table to return the remaining columns that match the values of the subquery:

select *
from mytable t1
inner join
(
  select A, max(b) B
  from mytable
  where b >50
  group by a
) t2
  on t1.a = t2.a
  and t1.b = t2.b
where t1.b >50

See SQL Fiddle with Demo

Problem

I have an aggregate function that does a group by (col A). It selects the maximum value from a set of columns(col B), but I also want to return another value from a column in the same row(col C). But if it groups 3 rows it selects the first value from column C not the column with the maximum (MAX(col B)). ``` A B C 1 75 jkl 1 100 abc 1 125 dae 2 200 def 3 300 ghi "SELECT A, MAX(B), C FROM myTable where B > 50 GROUP BY A" returns (first row) A => 1, B => 125, C => jkl I want it to return A => 1, B => 125, C => dae ```

Original source