zagors February 2016

oracle group related subquery

Helo, I have 2 simple tables and I must make one query which group row "GRO" from PROD and return "gro" which is used most often by coid. I try use subquery but i have error, I dont know how to transform this query to work.

Please help me :)

I use Oracle SQL .

TABLE CO

ID |name|param|
---+----+-----+
1  |AA  | X   |
2  |BB  | X   |
3  |CC  | X   |
4  |DD  | X   |
5  |EE  |     |

Table PROD

id| coid |gro
--+------+------
1 |  1   | a
2 |  1   | a
3 |  1   | b
4 |  1   | c
5 |  1   | d
6 |  2   | b
7 |  2   | c
8 |  2   | c
9 |  3   | a
10|  3   | a
11|  4   | b
12|  4   | b
13|  4   | b
14|  4   | a
15|  1   | a

Result

ID |name|best_gro|
---+----+--------+
1  |AA  | a      |
2  |BB  | c      |
3  |CC  | a      |
4  |DD  | b      |

My Query

select c.id, c.name,
(
    select gro from(
    SELECT  GRO, count(GRO) as m FROM HR.PROD pro
      where coid = c.id        --<------ ERROR 
    group by gro order by m desc
    ) where rownum <=1
) as best_gro

from HR.co c 
where c.param = 'X'

Answers


Alex Poole February 2016

You can't refer to your c column more than one level of subquery below where it is defined.

You can rewrite this with a join, using the analytic row_number() function, something like:

select id, name, gro
from (
  select id, name, gro,
    row_number() over (partition by id, name order by cnt desc) rnk
  from (
    select c.id, c.name, p.gro, count(*) as cnt
    from HR.co c
    join HR.prod p on p.coid = c.id
    where c.param = 'X'
    group by c.id, c.name, p.gro
  )
)
where rnk = 1

Depending on what you want to do with ties - if two or more gro values have the same count for a compary - you need the order by to specify how to choose which to use; or use rank or dense_rank instead of row_number to get all tied values.

Or you can use the keep dense rank approach which will be shorter.


zagors February 2016

Thanks Alex, i modified a little and i got correct result !

select id, name, gro from(
          select id, name, gro,
            rank() over (partition by coid order by cnt desc) rnk
          from (
            select c.id, c.name, p.gro, p.coid, count(*) as cnt
            from HR.co c
            join HR.prod p on p.coid = c.id
            where c.param = 'X'
            group by c.id, c.name, p.gro, p.coid   
          )
          )
      where rnk = 1

Post Status

Asked in February 2016
Viewed 3,145 times
Voted 10
Answered 2 times

Search




Leave an answer