Select group with maximum value of aggregate function

Question

I have this query

       select dca_sif, maxupp
       from (select d.sifra dca_sif , sum(col1) as maxup
              from tableD d join tablev v on d.id=v.d_id
             join tableT t on v.id=t.v_id 
            group by d.sif
            order by maxup desc
           )
      where rownum=1;

This returns first value, but If I have more groups with same maxup, how can I return all of them? If I delete rownum=1, it returns all groups and coresponding maxup. I want to return max(sum(col1)) but this cause error. Database is oracle.

http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group — , Aug 14 '14 at 09:36
That is not what I asked. I want only one single group with largest value, not largest value in each group — Error, Aug 14 '14 at 10:53

score 1 · Answer 1 · answered Aug 14 '14 at 11:04

1

You can try somthing like this:-

SELECT dca_sif, MAX(maxup)
FROM (SELECT d.sifra dca_sif , SUM(col1) AS maxup
      FROM tableD d JOIN tablev v ON d.id=v.d_id
      JOIN tableT t ON v.id=t.v_id 
      GROUP BY d.sif
      ORDER BY maxup DESC
      )
WHERE ROWNUM=1
GROUP BY dca_sif;

This might be helpful to you.

answered Aug 14 '14 at 11:04

Ankit Bajpai

13,128
4
25
40

1

Wouldn't it, because of rownum=1, return only one dca_sif, if there are more maxup with same value for different dca_sif? – Error Aug 14 '14 at 11:06

Thomas Köhne · Answer 2 · 2014-08-14T15:02:47.693

You can combine your select with the same select, but without the rownum limitation. Than join on the max id. There are many tables in your statement, so it is difficult to test for me, but this should work:

SELECT count(qry2.dcasif) || ' groups', sum( qry2.maxup2)
          FROM (  SELECT dca_sif, MAX (maxup) AS maxup1
                    FROM (  SELECT d.sifra dca_sif, SUM (col1) AS maxup
                              FROM tabled d
                                   JOIN tablev v ON d.id = v.d_id
                                   JOIN tablet t ON v.id = t.v_id
                          GROUP BY d.sif
                          ORDER BY maxup DESC)
                   WHERE ROWNUM = 1
                GROUP BY dca_sif) qry1
             , (  SELECT dca_sif, MAX (maxup) AS maxup2
                    FROM (  SELECT d.sifra dca_sif, SUM (col1) AS maxup
                              FROM tabled d
                                   JOIN tablev v ON d.id = v.d_id
                                   JOIN tablet t ON v.id = t.v_id
                          GROUP BY d.sif
                          ORDER BY maxup DESC)
                GROUP BY dca_sif) qry2
         WHERE qry1.maxup1 = qry2.maxup2

score 1 · Answer 3 · answered Aug 14 '14 at 17:12

1

SELECT dca_sif, maxup 
FROM
(SELECT a.*,rank() over(order by maxup desc)as Rank
FROM (SELECT d.sifra dca_sif , SUM(col1) AS maxup
  FROM tableD d JOIN tablev v ON d.id=v.d_id
  JOIN tableT t ON v.id=t.v_id 
  GROUP BY d.sif

  )a)
WHERE Rank=1

answered Aug 14 '14 at 17:12

Sun21

34
3

You should provide an (English) explanation of why this solves the OP's problem. What language features are you using to address the issue? – Nathaniel Ford Aug 14 '14 at 17:51

Select group with maximum value of aggregate function

3 Answers3