CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Craig Stevensson
Craig Stevensson

Reputation: 1336

Subquery based on max number of related rows

I'm using Oracle 11g R2.

I want to return a single value from a table which has multiple child rows. The subquery must return the value based on the max number of values of a second unrelated column.

The following code provides data in form of CTEs... and a failed attempt to create the statement. I know I have to differentiate somehow the values so that it returns the one that has the max number of child.

Check on the snippet "Expected result".

WITH GRPS(GRP, SUBGRP)
AS
(
    SELECT 'AAA', 'SUB_A1' FROM DUAL UNION ALL
    SELECT 'AAA', 'SUB_A2' FROM DUAL UNION ALL
    SELECT 'AAA', 'SUB_A2' FROM DUAL UNION ALL
    SELECT 'AAA', 'SUB_A2' FROM DUAL UNION ALL
    SELECT 'AAA', 'SUB_A3' FROM DUAL UNION ALL
    SELECT 'BBB', 'SUB_B5' FROM DUAL UNION ALL
    SELECT 'BBB', 'SUB_B6' FROM DUAL UNION ALL
    SELECT 'BBB', 'SUB_B6' FROM DUAL UNION ALL 
    SELECT 'BBB', 'SUB_B7' FROM DUAL 
),
TAB1 (GRP)
AS
(
   SELECT 'AAA' FROM DUAL UNION ALL
   SELECT 'BBB' FROM DUAL 
)

(


SELECT 
    TAB1.GRP,
    GRPS.SUBGRP, 
    COUNT(*)
FROM TAB1
JOIN GRPS 
    ON (GRPS.GRP = TAB1.GRP)
GROUP BY TAB1.GRP,
GRPS.SUBGRP 

-- That will get me these:
-- Row#	GRP	SUBGRP	COUNT(*)
-- 1	BBB	SUB_B5	1
-- 2	AAA	SUB_A2	3
-- 3	AAA	SUB_A1	1
-- 4	BBB	SUB_B6	2
-- From which I still have to perform a search to get me the rows with SUB_B6 AND SUB_A2


-- Expected result
-- AAA SUB_A2
-- BBB SUB_B6

)
;

This, of course, will not work and get me an error like "single-row subquery returns more than one row". I dont' need to get just any row from the correlated subquery. I need to get the value from which there are more repeated from that table. In this case SUB_A2, AND SUB_B6.

Upvotes: 0

Views: 62

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Use Analytic Function. Try this

WITH GRPS(GRP, SUBGRP)
AS
(
    SELECT 'AAA', 'SUB_A1' FROM DUAL UNION ALL
    SELECT 'AAA', 'SUB_A2' FROM DUAL UNION ALL
    SELECT 'AAA', 'SUB_A2' FROM DUAL UNION ALL
    SELECT 'AAA', 'SUB_A2' FROM DUAL UNION ALL
    SELECT 'BBB', 'SUB_B5' FROM DUAL UNION ALL
    SELECT 'BBB', 'SUB_B6' FROM DUAL UNION ALL
    SELECT 'BBB', 'SUB_B6' FROM DUAL 
),
TAB1 (GRP)
AS
(
   SELECT 'AAA' FROM DUAL UNION ALL
   SELECT 'BBB' FROM DUAL 
)

(

select grp,subgrp from(
SELECT 
    TAB1.GRP,
    GRPS.SUBGRP, 
    row_number() over(partition by TAB1.GRP order by  count(1) desc) rn
FROM TAB1
JOIN GRPS 
    ON (GRPS.GRP = TAB1.GRP)
  group by TAB1.GRP,
    GRPS.SUBGRP
) a where rn=1
)
;

FIDDLE DEMO

Upvotes: 1

Related Questions