Oracle SQL regarding subquery condition

Question

I got a question about Oracle SQL using the subquery.

With a table as shown below

enter image description here

the question is.

"Write a query that will display the customer(s) that referred maximum number of customers."

You can see a customer with max number of reference when you execute this code,

SELECT cust_referred, COUNT(*) 
FROM customer
WHERE cust_referred IS NOT NULL
GROUP BY cust_referred;

I think I need to match cust_num(previous table) to cust_referred with the maximum number of reference, which is 1003 with 3.

By mistake, we can't see talbe that I uploaded.. I tried SELECT cust_num, cust_fname, cust_lname FROM customer GROUP BY cust_num, cust_fname, cust_lname HAVING cust_num = ( SELECT cust_referred FROM (SELECT cust_referred, COUNT(*) FROM customer WHERE cust_referred IS NOT NULL GROUP BY cust_referred) WHERE COUNT(*) = MAX(COUNT(*)) ); — Sunjae Lee, May 18 '17 at 09:32
Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer — , May 18 '17 at 09:43
Sounds like you want to use analytic functions to find the max number of referred customers and then use that to filter the rows (ie. `where max_num_referred_cust = num_referred_cust`) — Boneist, May 18 '17 at 10:50

score 0 · Answer 1 · answered May 18 '17 at 10:04

0

If I have understood your problem correctly, I think this is what you are looking for

select cust_num, count(cust_refferred) from customer where cust_referred IS NOT NULL group by cust_num order by 2 desc

answered May 18 '17 at 10:04

Prateek

76
1
4

I think you are saying that I can pick the first or last row after ordering. However I can't pick just one if there are more than one maximum number.. – Sunjae Lee May 18 '17 at 10:16

score 0 · Answer 2 · answered May 19 '17 at 10:47

cust_referred is the Customer who did the referring. So the base query you want is

SELECT cust_referred
       , COUNT(*) as referrals
FROM customer
WHERE cust_referred IS NOT NULL
GROUP BY cust_referred
;

Now it becomes a top-n question.

"I can't pick just one if there are more than one maximum number.."

In Oracle 12c there is the marvelous FETCH clause which makes this simple:

SELECT cust_referred
       , COUNT(*) as referrals
FROM customer
WHERE cust_referred IS NOT NULL
GROUP BY cust_referred
order by 2 desc
fetch first 1 rows with ties;

This (dodgy English grammar but legit Oracle 12c) will return the cust_refered with the highest number of referrals, or all of them if there is a tie.

In earlier Oracle versions there are several different implementations but they are all clunkier. For instance:

with cte as (  
    SELECT cust_referred
           , COUNT(*) as referrals
    FROM customer
    WHERE cust_referred IS NOT NULL
    GROUP BY cust_referred
)
select *
from cte
where cte.referrals = ( select max(referrals) from cte)
/

Oracle SQL regarding subquery condition

2 Answers2