How to get the complete row from a maximum calculation?

group-by, join, oracle, sql

Solution

You can use the `FIRST` aggregation modifier to selectively apply your function over a subset of rows of a group -- here a single row (SQLFiddle demo):

SELECT xd.num1||xd.num2 nummer, 
       MAX(xg.groupname) KEEP (DENSE_RANK FIRST 
                               ORDER BY ranking.goodness DESC) grp,
       max(ranking.goodness)
FROM xxdata xd
    , xxgroups xg
    ,( select state_a, state_b, r as goodness
       from dual
       model return updated rows
       dimension by (0 state_a, 0 state_b) measures (0 r)
       rules (r[1,4]=90, r[3,7]=80,r[5,7]=70, r[4,7]=60, r[0,7]=50, r[0,4]=40)
       order by goodness desc
      ) ranking
WHERE xd.groupid=xg.groupid
  and ranking.state_a (+) = xd.state_a
  and ranking.state_b (+) = xd.state_b
GROUP BY xd.num1||xd.num2
ORDER BY nummer;

Your method with analytics works as well but since we already use aggregations here, we may as well use the `FIRST` modifier to get all columns in one go.

Problem

I do struggle with a `GROUP BY` -- again. The basics I can handle, but there it is: How do I get to different columns I named in the `group by`, without destroying my grouping? Note that `group by` is only my own idea, there may be others that work better. It must work in Oracle, though. Here is my example: ``` create table xxgroups ( groupid int not null primary key, groupname varchar2(10) ); insert into xxgroups values(100, 'Group 100'); insert into xxgroups values(200, 'Group 200'); drop table xxdata; create table xxdata ( num1 int, num2 int, state_a int, state_b int, groupid int, foreign key (groupid) references xxgroups(groupid) ); -- "ranks" are 90, 40, null, 70: insert into xxdata values(10, 10, 1, 4, 100); insert into xxdata values(10, 10, 0, 4, 200); insert into xxdata values(11, 11, 0, 3, 100); insert into xxdata values(20, 22, 5, 7, 200); ``` The task is to create a result-row for each `distinct (num1, num2)` and print that `groupname` with the highest calculated "rank" from `state_a` and `state_b`. Note that the first two rows have the same `num`s and thus only the higher ranking should be selected -- with the `groupname` being "Group 200". I got quite far with the basic `group by`, I think. ``` SELECT xd.num1||xd.num2 nummer, max(ranking.goodness) FROM xxdata xd , xxgroups xg ,( select state_a, state_b, r as goodness from dual model return updated rows dimension by (0 state_a, 0 state_b) measures (0 r) rules (r[1,4]=90, r[3,7]=80,r[5,7]=70, r[4,7]=60, r[0,7]=50, r[0,4]=40) order by goodness desc ) ranking WHERE xd.groupid=xg.groupid and ranking.state_a (+) = xd.state_a and ranking.state_b (+) = xd.state_b GROUP BY xd.num1||xd.num2 ORDER BY nummer ; ``` The result is 90% of what I need: ``` nummer ranking ---------------- 1010 90 1111 2022 70 ``` 100% perfect would be ``` nummer groupname ------------------- 1010 Group 100 1111 Group 100 2022 Group 200 ``` - The tricky part is, that I want the `groupname` in the result. And I can not include it in the `select`, because then I would have to put it into the `group by` as well -- which I do not want (then I would not select the best ranking entry from over all groups) - In my solution a use a `model` table to calculate the "rank". There are other solution I am sure. The point is, that it is a non-trivial calculation that I do not want to do twice. - I know from other examples that one could use a second query to get back to the original row to get to the `groupname`, but I can not see how I could to this here, without duplicating my ranking calculation. - A nice suggestion was to replace the `group by` with a `LIMIT 1`/`ORDER BY goodness` and use this calculating select as a filtering subselect. But a) there is no `LIMIT` in Oracle, and I doubt a `rownum<=1` would do in a subselect and b) I can not wrap my brain around it anyway. Maybe there is a way?

Original source