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
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