CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
abderrahim
abderrahim

Reputation: 33

how can i select top 5 in a select query

i have two tables clienteff3v9 and problemtransfereff3v9 i wanna select the 5 unlucky customers the unlucky customer is the customer with the maximum number of problems

create table clienteff3v9(
    cin varchar2(20) primary key,
    nom varchar2(20),
    compte varchar2(20)
);

create table problemtransfereff3v9(
    idp varchar2(20) primary key,
    cin varchar2(20),
    constraint fk foreign key(cin) references clienteff3v9(cin) on delete cascade
);

Upvotes: 0

Views: 228

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You don't need to join two tables, since you're using cin(client name) as key column to construct foreign key & primary key relation.

So, you may use one of the following methods(each one was tested) :

select cnt problem_count, cin client_name
  from
       (
    select cin, count(1) cnt
      from problemtransfereff3v9
         group by cin
         order by count(1) desc
       )
  where rownum <= 5;



with qry as
      (
    select cin, count(1) cnt
      from problemtransfereff3v9
         group by cin
         order by count(1) desc
       )
select cnt problem_count, cin client_name
  from qry       
  where rownum <= 5;



-- if you're on db 12c, use the following queries alternatively :

 select cin client_name, count(1) cnt problem_count
   from problemtransfereff3v9
   group by cin
   order by count(1) desc
   fetch first 5 rows only;



  select cin client_name, count(1) cnt problem_count
    from problemtransfereff3v9
   group by cin
   order by count(1) desc
   fetch first 5 rows with ties; 
   -- using WITH TIES may result to show more rows than 5 if matches more than 5(with all equal matching occurrences).

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74710

Your table structure isn't really that understandable - as a side note these days we try and get away from terseness (the unix days of cp, mv, ls and similar "try and make everything as short as possible, so you need a planet sized brain to remember everything" are thankfully fading)

As a result, I'm going to invent a pair of tables that are better named:

Customer
cust_id, name

ProblemReport
prob_id, cust_id, details

And leave you to adapt the following to your sitution:

SELECT * 
FROM
(
  SELECT * 
  FROM 
  (
    SELECT cust_id, COUNT(*) as probs 
    FROM ProblemReport 
    GROUP BY cust_id 
    ORDER BY probs DESC
  ) a 
  WHERE ROWNUM <=5
) probs
INNER JOIN
Customer c
ON
  probs.cust_id = c.cust_id

We take our problems and group them up per customer, ordering by the descending count. We limit the results after the ordering to 5, to give the top 5 unlucky customers. This is all done as a subquery. Technically we didn't have to output the COUNT(*) as probs, but it shows how unlucky each customer is (the number of problems they have)

Then we join the restricted set of unlucky customer IDs, to the customers table to get their details

Upvotes: 1

Related Questions