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
See SQL Fiddle with Demo
since you haven't mention the RDBMS you are using, use this query which works on almost all RDBMS
SELECT a.*
FROM tableName a
INNER JOIN
(
SELECT A, MAX(b) max_B
FROM tableName
WHERE b > 50
GROUP BY A
) b ON a.A = b.A AND
a.B = b.max_B
But if your RDBMS support window functions, you can use DENSE_RANK()
SELECT A, B, C
FROM
(
SELECT A, B, C,
DENSE_RANK() OVER (PARTITION A ORDER BY B DESC) rn
FROM tableName
WHERE b > 50
GROUP BY
) a
WHERE rn = 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With