How to get the full line from the maximum calculation?

Question

I'm fighting GROUP BY again. The basics I can handle, but here it is: How do I get into the different columns that I named in GROUP BY without destroying my grouping? Please note that GROUP BY is just my own idea, there may be others that work better. However, it should work in Oracle.

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 string for each distinct (num1, num2) and print groupname with the highest calculated "rank" from state_a and state_b .

Please note that the first two lines have the same num , and therefore you should choose only a higher rating - with groupname being "Group 200".

I'm pretty 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 difficult part is that I want to groupname in the result. And I cannot include it in select , because then I would have to put it in GROUP BY , which I do not want (then I would not select the best rating entry from all groups)
• In my solution, use the model table to calculate the "rank". There are other solutions that I'm sure. The fact is that this is a nontrivial calculation, which I do not want to do twice.
• I know from other examples that you can use the second query to return to the original line to go to groupname , but I cannot figure out how I could do this here without duplicating my ranking.
• A good suggestion was to replace GROUP BY with LIMIT 1 / ORDER BY goodness and use this computational choice as a filtering subsection. But a) there is no LIMIT in Oracle, and I doubt that rownum<=1 will do in the subquery, and b) I still can’t wrap my brain around me. Maybe there is a way?

Answer 1

You can use the FIRST aggregation modifier to selectively apply your function over a subset of the group lines - here is one line ( 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 analytics method also works, but since we already use aggregations here, we can also use the FIRST modifier to get all columns at once.

Answer 2

Who I was looking for before, but now I have found this answer , which I could take to my question. Oracle-solution here is over , partition by to order by and row_number() :

 select * from ( select data.*, row_number() over (partition by nummer order by goodness desc) as seqnum from ( SELECT xd.num1, xd.num2 nummer, xg.groupname, 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) ) ranking WHERE xd.groupid=xg.groupid and ranking.state_a (+) = xd.state_a and ranking.state_b (+) = xd.state_b ORDER BY nummer ) data ) where seqnum = 1 ; 

Result

  10 10 Group 100 90 1 11 11 Group 100 1 20 22 Group 200 70 1 

which is beautiful.

Now I have to try to understand what over in select really does ....