CODEWITHSUNDEEP
sqloracle-database
macintosh
macintosh

Reputation: 161

SQL: Find max number of rows

I am totally new to coding so might be my question is silly sorry about it first.

I have a database that has CUST_REFERRED represent CUST_NUMBER who referred book someone

CUST_NUM NAME_S     NAME_F      ADDRESS            Z_CODE          CUST_REFERRED
1001    MORALES     BONITA  P.O. BOX 651            32328   
1002    THOMPSON    RYAN    P.O. BOX 9835           90404   
1003    SMITH       LEILA   P.O. BOX 66             32306   
1004    PIERSON     THOMAS  69821 SOUTH AVENUE      83707   
1005    GIRARD      CINDY   P.O. BOX 851            98115   
1006    CRUZ        MESHIA  82 DIRT ROAD            12211   
1007    GIANA       TAMMY   9153 MAIN STREET        78710            1003
1008    JONES       KENNETH P.O. BOX 137            82003   
1009    PEREZ       JORGE   P.O. BOX 8564           91510            1003
1010    LUCAS       JAKE    114 EAST SAVANNAH       30314   
1011    MCGOVERN    REESE   P.O. BOX 18             60606   
1012    MCKENZIE    WILLIAM P.O. BOX 971            02110   
1013    NGUYEN      NICHOLAS    357 WHITE EAGLE AVE 34711            1006
1014    LEE         JASMINE P.O. BOX 2947           82414   
1015    SCHELL      STEVE   P.O. BOX 677            33111   
1016    DAUM        MICHELL 9851231 LONG ROAD       91508            1010
1017    NELSON      BECCA   P.O. BOX 563            49006   
1018    MONTIASA    GREG    1008 GRAND AVENUE       31206   
1019    SMITH       JENNIFER    P.O. BOX 1151       07962            1003
1020    FALAH       KENNETH P.O. BOX 335            08607

My idea is to find customer who referred max book. So as you can see 3 times 1003 number referred book who name is LEILA SMITH

I tried a code that;

SELECT
  CUST_REFERRED,
  COUNT(*)
FROM
  CUSTOMER
GROUP BY
  CUST_REFERRED
  ORDER BY CUST_REFERRED ASC;

This code gives me:

 1003          3
 1006          1
 1010          1

First, my question is I could not use LIMIT function to find max number and the second question is How Can I add more information of customer?

Upvotes: 2

Views: 160

Answers (5)

MT0
MT0

Reputation: 167962

You can do it in a single table scan (i.e. without any self joins) using analytic functions:

SELECT *
FROM   (
  SELECT t.*,
         MIN( CUST_REFERRED )
           KEEP ( DENSE_RANK FIRST ORDER BY num_referrals DESC )
           OVER ()
           AS best_referrer
  FROM   (
    SELECT c.*,
           COUNT( CUST_REFERRED )
             OVER ( PARTITION BY CUST_REFERRED )
             AS num_referrals
    FROM   CUSTOMER c
  ) t
)
WHERE  cust_num = best_referrer;

Upvotes: 0

Slkrasnodar
Slkrasnodar

Reputation: 824

edited to add the referred customer details. 

with data (cust_num, name_s, name_f, addr, code, cust_referred) as
    (
    /* begin: test data */
    select 1001    ,'MORALES     ','BONITA  ','P.O. BOX 651            ',32328,  null from dual union all   
    select 1002    ,'THOMPSON    ','RYAN    ','P.O. BOX 9835           ',90404,  null from dual union all 
    select 1003    ,'SMITH       ','LEILA   ','P.O. BOX 66             ',32306,  null from dual union all 
    select 1004    ,'PIERSON     ','THOMAS  ','69821, SOUTH AVENUE     ',83707,  null from dual union all 
    select 1005    ,'GIRARD      ','CINDY   ','P.O. BOX 851            ',98115,  null from dual union all 
    select 1006    ,'CRUZ        ','MESHIA  ','82 DIRT ROAD            ',12211,  null from dual union all 
    select 1007    ,'GIANA       ','TAMMY   ','9153 MAIN STREET        ',78710,            1003 from dual union all
    select 1008    ,'JONES       ','KENNETH ','P.O. BOX 137            ',82003,  null from dual union all 
    select 1009    ,'PEREZ       ','JORGE   ','P.O. BOX 8564           ',91510,            1003 from dual union all
    select 1010    ,'LUCAS       ','JAKE    ','114 EAST SAVANNAH       ',30314,  null from dual union all 
    select 1011    ,'MCGOVERN    ','REESE   ','P.O. BOX 18             ',60606,   null from dual union all
    select 1012    ,'MCKENZIE    ','WILLIAM ','P.O. BOX 971            ',02110,   null from dual union all
    select 1013    ,'NGUYEN      ','NICHOLAS    ','357 WHITE EAGLE AVE ',34711,            1006 from dual union all
    select 1014    ,'LEE         ','JASMINE ','P.O. BOX 2947           ',82414,   null from dual union all
    select 1015    ,'SCHELL      ','STEVE   ','P.O. BOX 677            ',33111,   null from dual union all
    select 1016    ,'DAUM        ','MICHELL ',',9851231, LONG ROAD     ',91508,            1010 from dual union all
    select 1017    ,'NELSON      ','BECCA   ','P.O. BOX 563            ',49006,   null from dual union all
    select 1018    ,'MONTIASA    ','GREG    ','1008 GRAND AVENUE       ',31206,   null from dual union all
    select 1019    ,'SMITH       ','JENNIFER    ','P.O. BOX 1151       ',07962,            1003 from dual union all
    select 1020    ,'FALAH       ','KENNETH ','P.O. BOX 335            ',08607,  null from dual
    /* end: test data */
    -- replace the above block with your table
    -- eg. select * from customers_table
    )
    , 
    max_referred as 
    (
      -- just interested in the first row after sorting by
      -- the count of referred column values
      select rownum, cust_referred, cnt from 
      (
        select cust_referred, count(cust_referred) cnt from data group by cust_referred order by 2 desc
      ) 
      where rownum = 1
    )
    -- joining on cust_referred column in *data* and *max_referred* tables to get the customer details
    -- and joining again to the *data* table for fetching the referred customer name
    select 
      cust.cust_num, cust.name_s, cust.name_f, cust.addr, cust.code, cust.cust_referred, ms.name_f || ms.name_s as "Referred Customer"
    from 
      data cust
     join 
      max_referred mr on (cust.cust_referred = mr.cust_referred)
      join 
      data ms
      on (mr.cust_referred = ms.cust_num)
    ;

Upvotes: 0

Axis
Axis

Reputation: 2132

SELECT NAME_F,
       NAME_S,
       ADDRESS,
       CUST_REFERRED
FROM CUSTOMER
WHERE CUST_NUM = (SELECT MOST_CUS_REF
                  FROM (SELECT CUST_REFERRED MOST_CUS_REF, COUNT(CUST_REFERRED)
                        MOST_CUS_REF_COUNT
                        FROM (SELECT CUST_REFERRED
                                 FROM customer
                                  WHERE cust_referred IS NOT NULL
                                 )
                        GROUP  BY CUST_REFERRED
                        HAVING COUNT(CUST_REFERRED) = (SELECT MAX (cust_ref_num)
                                                       FROM (SELECT CUST_REFERRED,
COUNT(CUST_REFERRED) cust_ref_num
                                                                   FROM (SELECT CUST_REFERRED
                                                                                FROM customer
                                                                                WHERE cust_referred IS NOT NULL
                                                                           )
                                                                GROUP BY CUST_REFERRED
                                                                )
                                                      )
                       )
                  )
;

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146469

try this: 

Select CUST_REFERRED, z.cnt from
   (SELECT CUST_REFERRED, COUNT(*) cnt
    FROM CUSTOMER where CUST_REFERRED is Not null
    GROUP BY CUST_REFERRED) Z
where z.cnt = 
     (select Max(cnt) from
          (SELECT COUNT(*) cnt
           FROM CUSTOMER where CUST_REFERRED is Not null
           GROUP BY CUST_REFERRED) ZZ)

Upvotes: 1

Maverick Sachin
Maverick Sachin

Reputation: 883

Try this query --

;WITH CTE
AS (
  SELECT CUSTOMER_REFID COUNT(*) AS REF_COUNT
  FROM CUSTOMER
  GROUP BY CUSTOMER_REFID
)
SELECT TOP 1 C2.CUSTOMER_ID
  ,C2.FIRST_NAME
  ,C2.LAST_NAME
  ,REF_COUNT
FROM CTE C1
INNER JOIN CUSTOMER C2 
ON C1.CUSTOMER_REFID = C2.CUSTOMER_ID
ORDER BY REF_COUNT DESC

Upvotes: 0

Related Questions