SQL Get Other Rows From Aggregate Function

Question

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

Taryn · Accepted Answer · 2013-02-14T17:24:41.297

15

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

edited Feb 14 '13 at 17:24

answered Feb 14 '13 at 17:16

Taryn

242,637
56
362
405

are nested SQL queries faster than say just collecting the MAX(B)'s and then running a single query for each of the MAX(B)'s? – The Hawk Feb 14 '13 at 17:22
@TheHawk I don't understand what you are asking, can you clarify? – Taryn Feb 14 '13 at 17:25
if I run the nested query by itself I get a result set with all max(b)'s. I loop through the result set of max(b)'s and run a select for each max(b). Basically the same thing as a nested query but kind of doing it manually – The Hawk Feb 14 '13 at 17:29
@TheHawk there would be no need to loop through the result when this gets you everything in one query. This query should be faster. – Taryn Feb 14 '13 at 17:30
1

Note that this solution will return multiple results if there are multiple rows with the same max value; for example if B of the third record were 100, not 125. If that's what you want, great; but if you want exactly one max record per group, see my post below. – bwperrin Mar 07 '14 at 23:25

score 5 · Answer 2 · answered Feb 14 '13 at 17:16

5

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

answered Feb 14 '13 at 17:16

John Woo

258,903
69
498
492

are nested SQL queries faster than say just collecting the MAX(B)'s and then running a single query for each of the MAX(B)'s? – The Hawk Feb 14 '13 at 17:22
It will be more efficient if you will add compound index on column A and B. – John Woo Feb 14 '13 at 17:34
Thanks for the DENSE_RANK note. Had the same question but for SQL Server – Scott Dec 26 '14 at 16:44

bwperrin · Answer 3 · 2013-02-14T18:20:29.180

This is a very common problem - "show me other columns on the rows matching my min()/max() aggregate criteria." On large tables, subquery strategies can become very slow, and ranking functions are sometimes not much better.

If you're willing to get your head around it, this is by far the most performant way to handle this (though again, not the most readable):

SELECT  A, cast(left(val, 8) as int) AS B, substring(val, 9, 999) AS C
FROM  ( SELECT A, max(str(B, 8) + C) AS val FROM myTable GROUP BY A) t

You can concatenate anything you want to what you're maxing, then extract it in the outer query. Voilá.

Note that this will return different results than the solutions posted by bluefeet and JW, in that if there are multiple matching max values per group, this method will pick a winner (the largest C) whereas the others will return multiple records. So, if the 3rd B value were 100 instead of 125, this will return 1, 100, dae whereas the other solutions would return both 1, 100, abd and 1, 100, dae.

This looks great. However, C is taken into account when the MAX function runs. It's MAXing over C as well, but I believe he wants the associated C value to "the MAXimum B for a particular A". That means in a tie, the query should have two results. This is because we are not supposed ot take the value of C into account. — VISQL, Aug 01 '14 at 20:51

SQL Get Other Rows From Aggregate Function

3 Answers3

Linked