Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Get Other Rows From Aggregate Function

Tags:

sql

mysql

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
like image 255
The Hawk Avatar asked Feb 14 '13 17:02

The Hawk


2 Answers

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

like image 182
Taryn Avatar answered Oct 17 '22 20:10

Taryn


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
  • SQLFiddle Demo

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

like image 5
John Woo Avatar answered Oct 17 '22 20:10

John Woo